stagfe2/app/Http/Controllers/PracticeManagementController.php

<?php

namespace App\Http\Controllers;

use App\Models\AppSession;
use App\Models\BillingReport;
use App\Models\CareMonth;
use App\Models\ClaimEDI;
use App\Models\ClientProChange;
use App\Models\Company;
use App\Models\Handout;
use App\Models\MBClaim;
use App\Models\Measurement;
use App\Models\Bill;
use App\Models\Claim;
use App\Models\Client;
use App\Models\McpRequest;
use App\Models\McCodeCheck;
use App\Models\Note;
use App\Models\Pack;
use App\Models\Pro;
use App\Models\Product;
use App\Models\ProFavorite;
use App\Models\ProGeneralAvailability;
use App\Models\ProProAccess;
use App\Models\ProRate;
use App\Models\ProSpecificAvailability;
use App\Models\ProSpecificUnavailability;
use App\Models\ProTeam;
use App\Models\ProTextShortcut;
use App\Models\ProTransaction;
use App\Models\Shipment;
use App\Models\SupplyOrder;
use App\Models\Team;
use App\Models\Ticket;
use App\Models\AccountInvite;
use App\Models\ClientMeasurementDaysPerMonth;
use App\Models\ClientBDTDevice;
use App\Models\ClientMemo;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Http;
use PDF;
use DateTime;
use DateTimeZone;
use Illuminate\Http\Request;
use App\Models\SegmentTemplate;
use App\Models\VisitTemplate;
use App\Models\VisitTemplateSegmentTemplate;

class PracticeManagementController extends Controller
{

    public function rpmMatrix(Request $request)
    {
        $proID = $this->performer()->pro->id;
        $isAdmin = $this->performer()->pro->pro_type == 'ADMIN';
        $query = Client::whereNull('shadow_pro_id');
        if(!$isAdmin) $query->where('mcp_pro_id', '=', $proID);
        $clients = $query->orderByRaw('most_recent_cellular_measurement_at desc nulls last')
            ->paginate(50);
        return view ('app.practice-management.rpm-matrix', compact('clients'));
    }

    public function mcCodeChecks(Request $request)
	{
		$checks = McCodeCheck::orderBy('next_eligible_date_professional', 'asc')->get();
		return view ('app.practice-management.mc-code-checks', compact('checks'));
	}

    public function remoteMonitoringReport(Request $request)
	{
		$rows = null;
		$proID = $this->performer()->pro->id;
		$isAdmin = $this->performer()->pro->pro_type == 'ADMIN';
		//$rows = $isAdmin ? ClientMeasurementDaysPerMonth::all() : ClientMeasurementDaysPerMonth::where('mcp_pro_id', $proID)->orderBy('year_month', 'asc')->orderBy('num_of_days_with_measurement', 'asc')->get();
        $query = CareMonth::whereNotNull('mcp_pro_id')
            ->where('number_of_days_with_remote_measurements', '>=', 16)
            ->where('days_between_most_recent_mcp_note_date_and_end_of_care_month', '<=', 90);
	if(!$isAdmin) $query->where('mcp_pro_id', '=', $proID);

    if($request->get('show_billing_not_closed_only')){
        $query->where(function($q){
            return $q->where('is_bill_closed', false)->orWhereNull('is_bill_closed');
        });
    }


	$rows = $query->orderByRaw(DB::raw('start_date DESC'))->paginate(100) ;
        return view ('app.practice-management.remote-monitoring-report', compact('rows', 'isAdmin'));
	}

    public function dailyTreatmentServices(Request $request) {
        $type = $request->input('t');
        $fPro = null;
        if($type === 'pro') {
            $fPro = Pro::where('uid', $request->input('p'))->first();
            $rows = DB::select(DB::raw("
SELECT effective_date, hcp_pro_id, p.name_first, p.name_last, count(*), sum(number_of_units) as units 
FROM bill JOIN pro p on p.id = bill.hcp_pro_id 
WHERE code = 'Treatment Services' 
GROUP BY effective_date, hcp_pro_id, p.name_first, p.name_last 
ORDER BY effective_date DESC, p.name_first ASC, p.name_last ASC
"));
        }
        else {
            $rows = DB::select(DB::raw("
SELECT effective_date, count(*), sum(number_of_units) as units FROM bill WHERE code = 'Treatment Services' GROUP BY effective_date ORDER BY effective_date DESC
"));
        }
        return view ('app.practice-management.daily-treatment-services', compact('rows'));
    }

    public function clientProChanges(Request $request)
    {
        $rows = ClientProChange::orderByRaw('created_at::date DESC')->orderBy('client_id', 'desc')->paginate(20);
        return view ('app.practice-management.client-pro-changes', compact('rows'));
    }

    public function billingReport(Request $request)
    {

        $claimStatus = $request->get('status');
        
        $rows = BillingReport::paginate(50);
     
        if($claimStatus){
            if($claimStatus == 'NO_CLAIMS'){
                $rows = BillingReport::whereHas('note', function($noteQuery) use ($claimStatus){
                    return $noteQuery->has('claims', '=', 0);
                })->whereHas('client', function($clientQuery){
                    return $clientQuery->where('client_engagement_status_category','<>' ,'DUMMY');
                })->orderBy('note_date', 'desc')->paginate(50);
            }elseif($claimStatus == 'NO_CLAIMS_AND_ZERO_DEDUCTIBLE'){
                $rows = BillingReport::whereHas('note', function($noteQuery) use ($claimStatus){
                    return $noteQuery->has('claims', '=', 0);
                })->whereHas('client', function($clientQuery){
                    return $clientQuery->where('client_engagement_status_category','<>' ,'DUMMY')
                    ->whereHas('latestClientPrimaryCoverage', function($lcpcQuery){
                        return $lcpcQuery->where('auto_medicare_mpb_deductible', 0);
                    });
                })->orderBy('note_date', 'desc')->paginate(50);
            }else{
                $rows =  BillingReport::whereHas('note', function($noteQuery) use ($claimStatus){
                    return $noteQuery->whereHas('claims', function($claimQuery) use ($claimStatus) {
                        return $claimQuery->where('status', $claimStatus);
                    });
                })->whereHas('client', function($clientQuery){
                    return $clientQuery->where('client_engagement_status_category','<>' ,'DUMMY');
                })->orderBy('note_date', 'desc')->paginate(50);
            }
        }else {
            $rows =  BillingReport::whereHas('client', function($clientQuery){
                return $clientQuery->where('client_engagement_status_category','<>' ,'DUMMY');
            })->orderBy('note_date', 'desc')->paginate(50);    
        }

        $claimStatuses = DB::select('SELECT distinct status FROM claim ORDER BY status DESC');

        return view('app.practice-management.billing-report', compact('rows', 'claimStatuses'));
    }

    public function dashboard(Request $request)
    {
        return view('app.practice-management.dashboard');
    }

    public function rates(Request $request, $selectedProUid = 'all')
    {
        $proUid = $selectedProUid ? $selectedProUid : 'all';
        $rates = ProRate::where('is_active', true);
        if ($proUid !== 'all') {
            $selectedPro = Pro::where('uid', $proUid)->first();
            $rates = $rates->where('pro_id', $selectedPro->id);
        }
        $rates = $rates->orderBy('pro_id', 'asc')->get();
        $pros = $this->pros;
        return view('app.practice-management.rates', compact('rates', 'pros', 'selectedProUid'));
    }

    public function previousBills(Request $request)
    {
        return view('app.practice-management.previous-bills');
    }

    public function financialTransactions(Request $request)
    {
        $pro = $this->performer()->pro;
        $transactions = null;
        if($pro->pro_type === 'ADMIN') {
            $transactions = ProTransaction::whereNotNull('id');
        }
        else {
            $transactions = ProTransaction::where('pro_id', $pro->id);
        }

        $filter = $request->input('p');
        if ($filter) {
            $filterPro = Pro::where('uid', $filter)->first();
            if($filterPro) {
                $transactions = $transactions->where('pro_id', '=', $filterPro->id);
            }
        }

        $filter = $request->input('t');
        if ($filter) {
            $transactions = $transactions->where('plus_or_minus', '=', $filter);
        }

        $filter = $request->input('c');
        if ($filter) {
            $transactions = $transactions->where('company_id', '=', $filter);
        }

        $filter = $request->input('bs');
        if ($filter) {
            $transactions = $transactions->where('created_at', '>=', $filter);
        }

        $filter = $request->input('be');
        if ($filter) {
            $transactions = $transactions->where('created_at', '<=', $filter);
        }

        $transactions = $transactions->orderBy('created_at', 'desc')->paginate();
        $companies = Company::where('is_active', true)->orderBy('name')->get();
        return view('app.practice-management.financial-transactions', compact('transactions', 'companies'));
    }

    public function pendingBillsToSign(Request $request)
    {
        return view('app.practice-management.pending-bills-to-sign');
    }

    public function HR(Request $request)
    {
        return view('app.practice-management.hr');
    }

    public function directDepositSettings(Request $request)
    {
        return view('app.practice-management.direct-deposit-settings');
    }

    public function w9(Request $request)
    {
        return view('app.practice-management.w9');
    }

    public function contract(Request $request)
    {
        return view('app.practice-management.contract');
    }

    public function notes(Request $request, $filter = '')
    {
        $proID = $this->performer()->pro->id;
        $query = Note::where('hcp_pro_id', $proID);
        switch ($filter) {
            case 'not-yet-signed':
                $query = $query->where('is_signed_by_hcp', false);
                break;

            case 'not-yet-signed-but-ally-signed':
                $query = $query->where('is_signed_by_hcp', false)->where('is_signed_by_ally', true);
                break;

            case 'without-bills':
                $query = $query->where('is_signed_by_hcp', true)->where('is_cancelled', false)->whereDoesntHave('bills');

                break;

            // more cases can be added as needed
            default:
                break;
        }
        $notes = $query->orderBy('created_at', 'desc')->get();
        return view('app.practice-management.notes', compact('notes', 'filter'));
    }

    public function dnaNotesPendingMcpSign(Request $request)
    {
        $proID = $this->performer()->pro->id;
        $notes = Note::where('ally_pro_id', $proID)
            ->where('is_signed_by_hcp', false)
            ->where('is_cancelled', false)
            ->orderBy('created_at', 'desc')
            ->get();
        return view('app.practice-management.dna-notes-pending-mcp-sign', compact('notes'));
    }

    public function naBillableSignedNotes(Request $request)
    {

        $notes = Note
            ::where('is_signed_by_hcp', TRUE)
            ->where('ally_pro_id', $this->performer()->pro->id)
            ->where('is_cancelled', FALSE)
            ->whereRaw("
            (
                SELECT count(bill.id)
                FROM bill WHERE
                      bill.is_cancelled = FALSE AND
                      bill.generic_pro_id = {$this->performer()->pro->id} AND
                      bill.note_id = note.id
            ) = 0
            ");

        $notes = $notes->orderBy('created_at', 'desc')->get();
        return view('app.practice-management.na-billable-signed-notes', compact('notes'));
    }

    public function bills(Request $request, $filter = '')
    {
        $proID = $this->performer()->pro->id;
        $query = Bill::where('is_cancelled', false)->where('bill_service_type', '<>', 'CARE_MONTH');
        switch ($filter) {
            case 'not-yet-signed':
                $query = $query
                    ->where(function ($q) use ($proID) {
                        $q->where(function ($q2) use ($proID) {
                            $q2->where('hcp_pro_id', $proID)->where('is_signed_by_hcp', false);
                        })
                            ->orWhere(function ($q2) use ($proID) {
                                $q2->where('cm_pro_id', $proID)->where('is_signed_by_cm', false);
                            })
                            ->orWhere(function ($q2) use ($proID) {
                                $q2->where('rme_pro_id', $proID)->where('is_signed_by_rme', false);
                            })
                            ->orWhere(function ($q2) use ($proID) {
                                $q2->where('rmm_pro_id', $proID)->where('is_signed_by_rmm', false);
                            })
                            ->orWhere(function ($q2) use ($proID) {
                                $q2->where('generic_pro_id', $proID)->where('is_signed_by_generic_pro', false);
                            });
                    });
                break;

            case 'previous':
                $query = $query
                    ->where(function ($q) use ($proID) {
                        $q->where(function ($q2) use ($proID) {
                            $q2->where('hcp_pro_id', $proID)->where('is_signed_by_hcp', true);
                        })
                            ->orWhere(function ($q2) use ($proID) {
                                $q2->where('cm_pro_id', $proID)->where('is_signed_by_cm', true);
                            })
                            ->orWhere(function ($q2) use ($proID) {
                                $q2->where('rme_pro_id', $proID)->where('is_signed_by_rme', true);
                            })
                            ->orWhere(function ($q2) use ($proID) {
                                $q2->where('rmm_pro_id', $proID)->where('is_signed_by_rmm', true);
                            });
                    });
                break;

            // more cases can be added as needed
            default:
                $query = $query
                    ->where(function ($q) use ($proID) {
                        $q->where(function ($q2) use ($proID) {
                            $q2->where('hcp_pro_id', $proID);
                        })
                            ->orWhere(function ($q2) use ($proID) {
                                $q2->where('cm_pro_id', $proID);
                            })
                            ->orWhere(function ($q2) use ($proID) {
                                $q2->where('rme_pro_id', $proID);
                            })
                            ->orWhere(function ($q2) use ($proID) {
                                $q2->where('rmm_pro_id', $proID);
                            });
                    });
                break;
        }
        $bills = $query->orderBy('created_at', 'desc')->get();
        return view('app.practice-management.bills', compact('bills', 'filter'));
    }

    public function rmBillsToSign(Request $request)
    {
        $performerProID = $this->performer()->pro->id;
        $bills = Bill::where('is_cancelled', false)->where('cm_or_rm', 'RM')
            ->where(function ($q) use ($performerProID) {
                $q
                    ->where(function ($q2) use ($performerProID) {
                        $q2->where('hcp_pro_id', $performerProID)->where('is_signed_by_hcp', false);
                    })
                    ->orWhere(function ($q2) use ($performerProID) {
                        $q2->where('rme_pro_id', $performerProID)->where('is_signed_by_rme', false);
                    })
                    ->orWhere(function ($q2) use ($performerProID) {
                        $q2->where('rmm_pro_id', $performerProID)->where('is_signed_by_rmm', false);
                    })
                    ->orWhere(function ($q2) use ($performerProID) {
                        $q2->where('generic_pro_id', $performerProID)->where('is_signed_by_generic_pro', false);
                    });
            })
            ->orderBy('effective_date', 'desc')
            ->get();

        return view('app.practice-management.rm-bills-to-sign', compact('bills'));
    }

    public function unacknowledgedCancelledBills(Request $request)
    {
        $bills = Bill::where('hcp_pro_id', $this->performer()->pro->id)
            ->where('is_cancelled', true)
            ->where('is_cancellation_acknowledged', false)
            ->orderBy('created_at', 'desc')
            ->get();
        return view('app.practice-management.unacknowledged-cancelled-bills', compact('bills'));
    }

    public function myTickets(Request $request, $filter = 'open')
    {
        $performer = $this->performer();
        $myTickets = Ticket::where(function ($q) use ($performer) {
            $q->where('assigned_pro_id', $performer->pro_id)
                ->orWhere('manager_pro_id', $performer->pro_id)
                ->orWhere('ordering_pro_id', $performer->pro_id)
                ->orWhere('initiating_pro_id', $performer->pro_id);
        });
        if ($filter === 'open') {
            $myTickets = $myTickets->where('is_open', true);
        } else if ($filter === 'closed') {
            $myTickets = $myTickets->where('is_open', false);
        }
        $myTickets = $myTickets->orderBy('created_at', 'desc')->get();
        return view('app.practice-management.my-tickets', compact('myTickets', 'filter'));
    }

    public function myTextShortcuts(Request $request)
    {

        $personalShortcuts = DB::table('pro_text_shortcut')
            ->leftJoin('pro', 'pro_text_shortcut.pro_id', '=', 'pro.id')
            ->select(
                'pro_text_shortcut.uid',
                'pro_text_shortcut.shortcut',
                'pro_text_shortcut.text',
                'pro.name_first',
                'pro.name_last'
            )
            ->where('pro_text_shortcut.is_removed', false);

        if($this->performer()->pro->pro_type !== 'ADMIN') {
            $personalShortcuts = $personalShortcuts->where('pro_id', $this->performer()->pro_id);
        }

        $personalShortcuts = $personalShortcuts
            ->orderBy('pro.name_last')
            ->orderBy('pro.name_first')
            ->orderBy('pro_text_shortcut.shortcut')
            ->get();

        $globalTextShortcuts = DB::table('pro_text_shortcut')
            ->select(
                'pro_text_shortcut.uid',
                'pro_text_shortcut.shortcut',
                'pro_text_shortcut.text'
            )
            ->whereNull('pro_id')
            ->where('pro_text_shortcut.is_removed', false)
            ->orderBy('pro_text_shortcut.shortcut')
            ->get();

        return view('app.practice-management.my-text-shortcuts', compact('personalShortcuts', 'globalTextShortcuts'));
    }

    public function myFavorites(Request $request, $filter = 'all')
    {
        $performer = $this->performer();
        $myFavorites = ProFavorite::where('pro_id', $performer->pro_id)->where('is_removed', false);
        if ($filter !== 'all') {
            $myFavorites = $myFavorites->where('category', $filter);
        }
        $myFavorites = $myFavorites
            ->whereIn('category', ['allergy', 'medication', 'problem'])
            ->orderBy('category', 'asc')
            ->orderBy('position_index', 'asc')
            ->get();
        return view('app.practice-management.my-favorites', compact('myFavorites', 'filter'));
    }

    public function patientsWithoutCoverage(Request $request, $filter = 'all')
    {
        $performer = $this->performer();

        $sql = "SELECT cl.uid, cl.name_first, cl.name_last FROM client cl ";

        $joinClause = 'LEFT JOIN client_primary_coverage cpc ON cl.latest_client_primary_coverage_id = cpc.id ';
        $withoutCondition = "cl.latest_client_primary_coverage_id IS NULL";
        $pendingCondition = "
(cl.latest_client_primary_coverage_id IS NOT NULL -- coverage exists, but status is null or unknown
    AND (
               (cpc.plan_type = 'MEDICARE' AND (cpc.is_partbprimary = 'UNKNOWN' OR cpc.is_partbprimary IS NULL))
               OR
               (cpc.plan_type != 'MEDICARE' AND
                (cpc.manual_determination_category = 'UNKNOWN' OR cpc.manual_determination_category IS NULL))
           ))";

        switch ($filter) {
            case 'without-coverage-information':
                $sql .= 'WHERE cl.shadow_pro_id IS NULL AND ';
                $sql .= $withoutCondition;
                break;
            case 'pending-coverage-verification':
                $sql .= $joinClause . 'WHERE cl.shadow_pro_id IS NULL AND ';
                $sql .= $pendingCondition;
                break;
            default:
                $sql .= $joinClause . 'WHERE cl.shadow_pro_id IS NULL AND ';
                $sql .= '(' . $withoutCondition . ' OR ' . $pendingCondition . ')';
                break;
        }

        $sql .= ' ORDER BY cl.name_first ASC';

        $pendingCoverage = DB::select(DB::raw($sql));

        return view('app.practice-management.patients-without-coverage', compact('pendingCoverage', 'filter'));
    }

    public function proAvailability(Request $request, $proUid = null)
    {
        $performer = $this->performer();
        $pro = $performer->pro;

        if ($proUid) {
            $pro = Pro::where('uid', $proUid)->first();
        }

        if ($request->get('pro_uid')) {
            $proUid = $request->get('pro_uid');
            $pro = Pro::where('uid', $proUid)->first();
        }

        $selectedProUid = $pro->uid;

        $pros = $this->pros;

        $generalAvailabilitiesList = ProGeneralAvailability::where('pro_id', $pro->id)->where('is_cancelled', false)->orderBy('created_at', 'asc')->get();
        $generalAvailabilities = [
            'MONDAY' => [],
            'TUESDAY' => [],
            'WEDNESDAY' => [],
            'THURSDAY' => [],
            'FRIDAY' => [],
            'SATURDAY' => [],
            'SUNDAY' => [],
        ];
        foreach ($generalAvailabilitiesList as $ga) {
            if ($ga->day_of_week == 'MONDAY') {
                $generalAvailabilities['MONDAY'][] = $ga;
            }
            if ($ga->day_of_week == 'TUESDAY') {
                $generalAvailabilities['TUESDAY'][] = $ga;
            }
            if ($ga->day_of_week == 'WEDNESDAY') {
                $generalAvailabilities['WEDNESDAY'][] = $ga;
            }
            if ($ga->day_of_week == 'THURSDAY') {
                $generalAvailabilities['THURSDAY'][] = $ga;
            }
            if ($ga->day_of_week == 'FRIDAY') {
                $generalAvailabilities['FRIDAY'][] = $ga;
            }
            if ($ga->day_of_week == 'SATURDAY') {
                $generalAvailabilities['SATURDAY'][] = $ga;
            }
            if ($ga->day_of_week == 'SUNDAY') {
                $generalAvailabilities['SUNDAY'][] = $ga;
            }
        }

        $specificAvailabilities = ProSpecificAvailability::where('pro_id', $pro->id)->where('is_cancelled', false)->orderBy('start_time')->get();
        $specificUnavailabilities = ProSpecificUnavailability::where('pro_id', $pro->id)->where('is_cancelled', false)->orderBy('start_time', 'asc')->get();

        //events for the calendar
        $startDate = date('Y-m-d', strtotime("sunday -1 week"));
        $endDateTime = new DateTime($startDate);
        $endDateTime->modify('+6 day');
        $endDate = $endDateTime->format("Y-m-d");

        $eventsData = $pro->getAvailabilityEvents($startDate, $endDate);
        $events = json_encode($eventsData);

        return view(
            'app.practice-management.pro-availability',
            compact(
                'pros',
                'generalAvailabilities',
                'specificAvailabilities',
                'specificUnavailabilities',
                'events',
                'selectedProUid'
            )
        );
    }

    public function loadAvailability(Request $request, $proUid)
    {
        $performer = $this->performer();
        $pro = $performer->pro;
        $startDate = $request->get('start');
        $endDate = $request->get('end');

        $selectedPro = Pro::where('uid', $proUid)->first();

        return $selectedPro->getAvailabilityEvents($startDate, $endDate, $request->input('tz') ? $request->input('tz') : 'EASTERN');
    }

    public function proAvailabilityFilter(Request $request)
    {
        $proUid = $request->get('proUid');
        return ['success' => true, 'data' => $proUid];
    }

    // video call page (RHS)
    // generic call handle (no uid)
    // specific call handle (uid of client)
    public function meet(Request $request, $uid = false)
    {
        $session = AppSession::where('session_key', $request->cookie('sessionKey'))->first();
        $client = !empty($uid) ? Client::where('uid', $uid)->first() : null;
        if (!empty($client)) {
            return view('app.video.call-minimal', compact('session', 'client'));
        }
        return view('app.video.call-agora-v2', compact('session', 'client'));
    }

    // check video page
    public function checkVideo(Request $request, $uid)
    {
        $session = AppSession::where('session_key', $request->cookie('sessionKey'))->first();
        $client = !empty($uid) ? Client::where('uid', $uid)->first() : null;
        $publish = false;
        return view('app.video.check-video-minimal', compact('session', 'client'));
    }

    public function getParticipantInfo(Request $request)
    {
        $sid = intval($request->get('uid')) - 1000000;
        $session = AppSession::where('id', $sid)->first();
        $result = [
            "type" => '',
            "name" => ''
        ];
        if ($session) {
            $result["type"] = $session->session_type;
            switch ($session->session_type) {
                case 'PRO':
                    $pro = Pro::where('id', $session->pro_id)->first();
                    $result["name"] = $pro->displayName();
                    break;
                case 'CLIENT':
                    $client = Client::where('id', $session->client_id)->first();
                    $result["name"] = $client->displayName();
                    break;
            }
        }
        return json_encode($result);
    }

    // ajax ep used by the video page
    // this is needed bcoz meet() is used not
    // just for the client passed to the view
    public function getOpentokSessionKey(Request $request, $uid)
    {
        $client = Client::where('uid', $uid)->first();
        return json_encode(["data" => $client ? $client->opentok_session_id : '']);
    }

    // poll to check if there are patients with active mcp requests
    public function getPatientsInQueue(Request $request)
    {

        $myInitiatives = $this->performer->pro->initiatives;
        if ($myInitiatives) {
            $myInitiatives = strtoupper($myInitiatives);
        }
        $myInitiativesList = explode('|', $myInitiatives);

        $myForeignLanguages = $this->performer->pro->foreign_languages;
        if ($myForeignLanguages) {
            $myForeignLanguages = strtoupper($myForeignLanguages);
        }
        $myForeignLanguagesList = explode('|', $myForeignLanguages);

        $clients = Client::whereNotNull('active_mcp_request_id')->where(function ($query) use ($myInitiativesList) {
            $query->whereNull('initiative')->orWhereIn('initiative', $myInitiativesList);
        })
            ->where(function ($query) use ($myForeignLanguagesList) {
                $query->whereNull('preferred_foreign_language')->orWhereIn('preferred_foreign_language', $myForeignLanguagesList);
            })->limit(3)->get();
        $results = [];


        foreach ($clients as $client) {
            $results[] = [
                'clientUid' => $client->uid,
                'name' => $client->displayName(),
                'initials' => substr($client->name_first, 0, 1) . substr($client->name_last, 0, 1)
            ];
        }

        return json_encode($results);

    }

    public function currentWork(Request $request)
    {
        return view('app/current-work');
    }

    public function calendar(Request $request, $proUid = null)
    {
        $pros = Pro::all();
        if ($this->pro && $this->pro->pro_type != 'ADMIN') {
            $accessiblePros = ProProAccess::where('owner_pro_id', $this->pro->id);
            $accessibleProIds = [];
            foreach ($accessiblePros as $accessiblePro) {
                $accessibleProIds[] = $accessiblePro->id;
            }
            $accessibleProIds[] = $this->pro->id;
            $pros = Pro::whereIn('id', $accessibleProIds)->get();
        }
        return view('app.practice-management.calendar', compact('pros'));
    }

    public function cellularDeviceManager(Request $request, $proUid = null)
    {
        $proUid = $proUid ? $proUid : $request->get('pro-uid');
        $performerPro = $this->performer->pro;
        $targetPro = null;
        $expectedForHcp = null;
        if ($performerPro->pro_type == 'ADMIN') {
            $targetPro = Pro::where('uid', $proUid)->first();
        } else {
            $targetPro = $performerPro;
        }
        $clients = [];
        if ($targetPro) {
            $clients = Client::where('mcp_pro_id', $targetPro->id)->orderBy('created_at', 'desc')->paginate(20);
        } else {
            $clients = Client::orderBy('created_at', 'desc')->paginate(20);
        }
        return view('app.practice-management.cellular-device-manager', compact('clients', 'targetPro', 'proUid'));
    }

    public function treatmentServiceUtil(Request $request)
    {
        $view_treatment_service_utilization_org = DB::select(DB::raw("SELECT * FROM view_treatment_service_utilization_org ORDER BY effective_date DESC"));
        $view_treatment_service_utilization = DB::select(DB::raw("SELECT * FROM view_treatment_service_utilization ORDER BY effective_date DESC, total_hrs DESC"));
        $view_treatment_service_utilization_by_patient = DB::select(DB::raw("SELECT * FROM view_treatment_service_utilization_by_patient ORDER BY pro_lname ASC, pro_fname ASC, hcp_pro_id ASC, total_hrs DESC"));
        return view('app.practice-management.treatment-services-util', compact(
            'view_treatment_service_utilization_org',
            'view_treatment_service_utilization',
            'view_treatment_service_utilization_by_patient'));
    }

    public function processingBillMatrix(Request $request, $proUid = null)
    {
        $proUid = $proUid ? $proUid : $request->get('pro-uid');
        $performerPro = $this->performer->pro;
        $targetPro = null;
        if ($performerPro->pro_type == 'ADMIN') {
            $targetPro = Pro::where('uid', $proUid)->first();
        } else {
            $targetPro = $performerPro;
        }

        $bills = Bill::where('is_cancelled', false);

        if(!$request->input('t') || $request->input('t') === 'hcp') {
            $bills = $bills
                ->where('has_hcp_been_paid', false)
                ->where('hcp_expected_payment_amount', '>', 0)
                ->where('is_signed_by_hcp', true);
            if ($targetPro) {
                $bills = $bills->where('hcp_pro_id', $targetPro->id);
            }
            if($request->input('c')) {
                $bills = $bills->whereRaw('(SELECT company_id from company_pro where id = hcp_company_pro_id) = ' . $request->input('c'));
            }
        }
        else if($request->input('t') === 'na') {
            $bills = $bills
                ->where('has_generic_pro_been_paid', false)
                ->where('generic_pro_expected_payment_amount', '>', 0)
                ->where('is_signed_by_generic_pro', true);
            if ($targetPro) {
                $bills = $bills->where('generic_pro_id', $targetPro->id);
            }
            if($request->input('c')) {
                $bills = $bills->whereRaw('(SELECT company_id from company_pro where id = generic_company_pro_id) = ' . $request->input('c'));
            }
        }

        $filter = $request->input('f');
        switch ($filter) {
            case 'verified':
                $bills = $bills->where('is_verified', true);
                break;
            case 'not-verified':
                $bills = $bills->where('is_verified', false);
                break;
        }

        $filter = $request->input('bs');
        if ($filter) {
            $bills = $bills->where('balance_post_date', '>=', $filter);
        }

        $filter = $request->input('be');
        if ($filter) {
            $bills = $bills->where('balance_post_date', '<=', $filter);
        }

        $filter = $request->input('s');
        if ($filter) {
            $bills = $bills->where('code', '=', $filter);
        }

        $bills = $bills->orderBy('effective_date', 'desc')->paginate();

        $companies = Company::where('is_active', true)->orderBy('name')->get();

        $codes = DB::select(DB::raw("SELECT code, count(*) as count FROM bill WHERE is_cancelled IS FALSE GROUP BY code ORDER BY code"));

        $viewData = [
            'bills' => $bills,
            'targetPro' => $targetPro,
            'performerPro' => $performerPro,
            'proUid' => $proUid,
            'companies' => $companies,
            'codes' => $codes
        ];
        return view('app.practice-management.processing-bill-matrix', $viewData);
    }

    public function hcpNoteActivity(Request $request)
    {

        $filters = $request->all();

        $from_date = $request->get('from_date');
//        if(!$from_date) {
//            $from_date = '2010-01-01';
//        }
        $to_date = $request->get('to_date');
//        if(!$to_date){
//            $to_date = '2900-12-31';
//        }
        

        //TODO sanitize inputs

        $proUid =  $request->get('pro-uid');

        $now = Carbon::now();
        $startOfCurrentMonth = $now->startOfMonth('Y-m-d');
        $startOfCurrentMonth = $startOfCurrentMonth->toDateString();

        $endOfCurrentMonth  = $now->endOfMonth()->format('Y-m-d');

        $rows = DB::table('pro')
        ->selectRaw(" *,
            (SELECT id FROM note WHERE note.hcp_pro_id = pro.id AND is_cancelled = false ORDER BY created_at DESC LIMIT 1) as last_note_id, 
            (SELECT note.created_at FROM note WHERE note.hcp_pro_id = pro.id AND note.is_cancelled = false ORDER BY note.created_at DESC LIMIT 1) as last_note_created_at,
            (SELECT COUNT(*) FROM note WHERE note.hcp_pro_id = pro.id AND note.is_cancelled = false AND created_at::DATE >= '${startOfCurrentMonth}'::DATE AND created_at::DATE <= '${endOfCurrentMonth}'::DATE ) AS notes_this_month,
            (SELECT COUNT(*) FROM client WHERE client.mcp_pro_id = pro.id AND client.client_engagement_status_category <> 'DUMMY' AND client.client_engagement_status_category <> 'DUPLICATE') as total_client_count,
            (SELECT COUNT(*) FROM client WHERE client.mcp_pro_id = pro.id AND client.client_engagement_status_category <> 'DUMMY' AND client.client_engagement_status_category <> 'DUPLICATE' AND client.most_recent_completed_mcp_note_date >= (NOW() - interval '60 days')::DATE) as active_client_count
            " . (!!$from_date && !!$to_date ? ",(SELECT COUNT(*) FROM note WHERE note.hcp_pro_id = pro.id AND note.is_cancelled = false AND created_at::DATE >= '${from_date}'::DATE AND created_at::DATE <= '${to_date}'::DATE  ) AS notes_this_period" : ""));

        $prosIDs = $request->get('pros_ids');
        if($prosIDs){
            $pros = 
            $rows = $rows->whereIn('pro.id', $prosIDs);
        }

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

        $rows = $rows->orderByRaw("$sortBy $sortDir NULLS LAST");

        $rows = $rows->paginate(50);


        return view('app.practice-management.hcp-note-activity', compact('rows', 'filters'));
    }


    public function proFinancials(Request $request, $proUid = null)
    {
        $proUid = $proUid ? $proUid : $request->get('pro-uid');
        $performerPro = $this->performer->pro;
        $targetPro = null;
        if ($performerPro->pro_type == 'ADMIN') {
            $targetPro = Pro::where('uid', $proUid)->first();
        } else {
            $targetPro = $performerPro;
        }
        $fPros = Pro::whereNotNull('balance');
        if($targetPro) {
            $fPros = $fPros->where('uid', $targetPro->uid);
        }
        $fPros = $fPros
            ->where('balance', '>', 0)
            ->orderBy('name_first')
            ->orderBy('name_last')
            ->paginate();
        return view('app.practice-management.pro-financials', compact('fPros', 'targetPro'));
    }

    public function hcpBillMatrix(Request $request, $proUid = null)
    {
        $proUid = $proUid ? $proUid : $request->get('pro-uid');
        $performerPro = $this->performer->pro;
        $targetPro = null;
        $allPros = [];
        $expectedForHcp = null;

        if ($performerPro->pro_type == 'ADMIN') {
            $allPros = Pro::all();
            $targetPro = Pro::where('uid', $proUid)->first();
        } else {
            $targetPro = $performerPro;
        }
        $rows = [];
        if ($targetPro) {
            $rows = DB::select(DB::raw("SELECT * FROM aemish_bill_report WHERE hcp_pro_id = :targetProID"), ['targetProID' => $targetPro->id]);
        } else {
            $rows = DB::select(DB::raw("SELECT * FROM aemish_bill_report"));
        }
        return view('app.practice-management.hcp-bill-matrix', compact('rows', 'allPros', 'expectedForHcp', 'targetPro', 'proUid'));
    }

    public function billingManager(Request $request, $proUid = null)
    {
        $proUid = $proUid ? $proUid : $request->get('pro-uid');
        $performerPro = $this->performer->pro;
        $targetPro = null;
        $allPros = [];
        $expectedForHcp = null;
        if ($performerPro->pro_type == 'ADMIN') {
            $allPros = Pro::all();
            $targetPro = Pro::where('uid', $proUid)->first();
        } else {
            $targetPro = $performerPro;
        }
        $notes = [];
        if ($targetPro) {
            $expectedForHcp = DB::select(DB::raw("SELECT coalesce(SUM(hcp_expected_payment_amount),0) as expected_pay FROM bill WHERE hcp_pro_id = :targetProID  AND is_signed_by_hcp IS TRUE AND is_cancelled = false"), ['targetProID' => $targetPro->id])[0]->expected_pay;
            $notes = Note::where('hcp_pro_id', $targetPro->id);
        } else {
            $notes = Note::where('id', '>', 0);
        }

        if($request->input('date')) {
            $notes = $notes->where('effective_dateest', $request->input('date'));
        }

        $filters = [];
        $filters['bills_created'] = $request->input('bills_created');
        $filters['is_billing_marked_done'] = $request->input('is_billing_marked_done');
        $filters['bills_resolved'] = $request->input('bills_resolved');
        $filters['bills_closed'] = $request->input('bills_closed');
        $filters['claims_created'] = $request->input('claims_created');
        $filters['claims_closed'] = $request->input('claims_closed');

        if ($filters['bills_created']) {
            $notes->where(
                'bill_total_expected',
                ($filters['bills_created'] === 'yes' ? '>' : '<='),
                0);
        }

        if ($filters['is_billing_marked_done']) {
            $notes->where(
                'is_billing_marked_done',
                ($filters['is_billing_marked_done'] === 'yes' ? '=' : '!='),
                true);
        }

        if ($filters['bills_resolved']) {
            $notes->whereRaw('(SELECT count(id) FROM bill WHERE note_id = note.id) > 0'); // have bills
            if ($filters['bills_resolved'] === 'yes') {
                $notes->whereRaw('(SELECT count(id) FROM bill WHERE note_id = note.id AND (is_cancelled = false  AND is_verified = false) OR (is_cancelled = TRUE AND is_cancellation_acknowledged = FALSE)) > 0');
            } elseif ($filters['bills_resolved'] === 'no') {
                $notes->whereRaw('(SELECT count(id) FROM bill WHERE note_id = note.id AND ((is_cancelled = true AND is_cancellation_acknowledged = true) OR is_verified = true)) = 0');
            }
        }

        if ($filters['bills_closed']) {
            $notes->where(
                'is_bill_closed',
                ($filters['bills_closed'] === 'yes' ? '=' : '!='),
                true);
        }

        if ($filters['claims_created']) {
            $notes->where(
                'claim_total_expected',
                ($filters['claims_created'] === 'yes' ? '>' : '<='),
                0);
        }

        if ($filters['claims_closed']) {
            $notes->where(
                'is_claim_closed',
                ($filters['claims_closed'] === 'yes' ? '=' : '!='),
                true);
        }

        $notes = $notes->orderBy('effective_dateest', 'desc')->paginate(20);

        return view('app.practice-management.billing-manager', compact('notes', 'allPros', 'expectedForHcp', 'targetPro', 'proUid', 'filters'));
    }

    public function remoteMonitoring(Request $request) {

        $performer = $this->performer();

        abort_if($performer->pro->pro_type !== 'ADMIN' && !$performer->pro->can_view_rm_matrix, 403);

        $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
        $careMonthStart = date($ym . '-01');

        $rc = $request->input('rc') ?: 1;
        $rc2 = $request->input('rc2') ?: 2;

        $conditions = $this->rpmConditions($performer, $rc, $rc2);

        $sortBy = $request->input('sort_by') ?: 'name_first';
        $sortDir = $request->input('sort_dir') ?: 'ASC';
        $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST";

        $query = "
SELECT client.name_first, client.name_last,
       (client.name_first || ' ' || client.name_last) as client_name,
       client.uid as client_uid, 
       client.dob,
       client.is_enrolled_in_rm,
       client.most_recent_completed_mcp_note_date,
       care_month.uid as care_month_uid,
       care_month.id as care_month_id,
       care_month.start_date,
       care_month.rm_total_time_in_seconds_by_mcp,
       care_month.number_of_days_with_remote_measurements,
       care_month.has_anyone_interacted_with_client_about_rm_outside_note,
       care_month.rm_num_measurements_not_stamped_by_mcp,
       care_month.rm_num_measurements_not_stamped_by_non_hcp,
       care_month.rm_num_measurements_not_stamped_by_rmm,
       care_month.rm_num_measurements_not_stamped_by_rme,
       client.mcp_pro_id,
       client.default_na_pro_id,
       client.rmm_pro_id,
       client.rme_pro_id,
       client.cell_number,
       client.most_recent_cellular_bp_dbp_mm_hg,
       client.most_recent_cellular_bp_sbp_mm_hg,
       client.most_recent_cellular_bp_measurement_at,      
       client.most_recent_cellular_weight_value,
       client.most_recent_cellular_weight_measurement_at
FROM care_month join client on care_month.client_id = client.id
WHERE
      client.mcp_pro_id = {$performer->pro->id}
      AND EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
      AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
      " . (count($conditions) > 0 ? 'AND ' . implode(" AND ", $conditions) : '') . "
      $orderBy
";

        $patients = DB::select($query);

        $timestamp = strtotime(date('Y-m-d'));
        $daysRemaining = (int)date('t', $timestamp) - (int)date('j', $timestamp);

        return view('app.practice-management.remote-monitoring', compact('patients', 'daysRemaining', 'careMonthStart'));
    }

    public function remoteMonitoringCount(Request $request) {

        $performer = $this->performer();

        $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
        $careMonthStart = date($ym . '-01');

        $rc = $request->input('rc') ?: 1;
        $rc2 = $request->input('rc2') ?: 2;

        $conditions = $this->rpmConditions($performer, $rc, $rc2);

        $count = DB::select(
            DB::raw(
                "
SELECT count(*)
FROM care_month join client on care_month.client_id = client.id
WHERE
      client.mcp_pro_id = {$performer->pro->id}
      AND EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
      AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
      " . (count($conditions) > 0 ? 'AND ' . implode(" AND ", $conditions) : '')
            )
        );

        return $count[0]->count;
    }

    public function remoteMonitoringAdmin(Request $request) {

        $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
        $careMonthStart = date($ym . '-01');

        $rc = $request->input('rc') ?: 1;
        $rc2 = $request->input('rc2') ?: 2;

        $conditions = $this->rpmConditionsAdmin($this->performer(), $rc, $rc2);

        $patients = DB::select(
            DB::raw(
                "
SELECT client.name_first, 
       client.name_last, 
       client.uid as client_uid, 
       client.dob,
       client.is_enrolled_in_rm,
       client.most_recent_completed_mcp_note_date,
       care_month.uid as care_month_uid,
       care_month.id as care_month_id,
       care_month.start_date,
       care_month.rm_total_time_in_seconds_by_mcp,
       care_month.number_of_days_with_remote_measurements,
       care_month.has_anyone_interacted_with_client_about_rm_outside_note,
       care_month.rm_num_measurements_not_stamped_by_mcp,
       care_month.rm_num_measurements_not_stamped_by_non_hcp,
       care_month.rm_num_measurements_not_stamped_by_rmm,
       care_month.rm_num_measurements_not_stamped_by_rme,
       client.mcp_pro_id,
       client.default_na_pro_id,
       client.rmm_pro_id,
       client.rme_pro_id,
       client.cell_number,
       client.most_recent_cellular_bp_dbp_mm_hg,
       client.most_recent_cellular_bp_sbp_mm_hg,
       client.most_recent_cellular_bp_measurement_at,      
       client.most_recent_cellular_weight_value,
       client.most_recent_cellular_weight_measurement_at
FROM care_month join client on care_month.client_id = client.id
WHERE
      client.shadow_pro_id is null AND client.is_enrolled_in_rm = 'YES'
      AND EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
      AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
      " . (count($conditions) > 0 ? 'AND ' . implode(" AND ", $conditions) : '') . "
ORDER BY care_month.number_of_days_with_remote_measurements DESC NULLS LAST, client.name_first, client.name_last
"
            )
        );

        $timestamp = strtotime(date('Y-m-d'));
        $daysRemaining = (int)date('t', $timestamp) - (int)date('j', $timestamp);

        return view('app.practice-management.remote-monitoring-admin', compact('patients', 'daysRemaining', 'careMonthStart'));
    }

    public function remoteMonitoringAdminCount(Request $request) {

        $ym = ($request->input('y') ?: 'Y') . '-' . ($request->input('m') ?: 'm');
        $careMonthStart = date($ym . '-01');

        $rc = $request->input('rc') ?: 1;
        $rc2 = $request->input('rc2') ?: 2;

        $conditions = $this->rpmConditionsAdmin($this->performer(), $rc, $rc2);

        $count = DB::select(
            DB::raw(
                "
SELECT count(*)
FROM care_month join client on care_month.client_id = client.id
WHERE
      client.shadow_pro_id is null AND client.is_enrolled_in_rm = 'YES'
      AND EXTRACT(MONTH from care_month.start_date) = " . ($request->input('m') ?: 'EXTRACT(MONTH from now())') . "
      AND EXTRACT(YEAR from care_month.start_date) = " . ($request->input('y') ?: 'EXTRACT(YEAR from now())') . "
      " . (count($conditions) > 0 ? 'AND ' . implode(" AND ", $conditions) : '')
            )
        );

        return $count[0]->count;
    }

    private function rpmConditions($performer, $rc, $rc2) {
        $conditions = [];

        $c_isMCP = "client.mcp_pro_id = {$performer->pro->id}";
        $c_enrolledInRPM = "client.is_enrolled_in_rm = 'YES'";
        $c_hasDevice = "(SELECT COUNT(client_bdt_device.id) FROM client_bdt_device JOIN bdt_device bd on client_bdt_device.device_id = bd.id WHERE client_bdt_device.client_id = client.id) > 0";
        $c_lastVisitBefore90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) > 90";
        $c_lastVisitWithin90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) <= 90";
        $c_notSpokenToThisMonth = "(care_month.has_anyone_interacted_with_client_about_rm_outside_note IS NULL OR care_month.has_anyone_interacted_with_client_about_rm_outside_note = FALSE)";
        $c_spokenToThisMonth = "care_month.has_anyone_interacted_with_client_about_rm_outside_note = TRUE";
        $c_hasUnstamped = "care_month.rm_num_measurements_not_stamped_by_mcp > 0";
        $c_hasNoUnstamped = "care_month.rm_num_measurements_not_stamped_by_mcp = 0";
        $c_lt16MeasurementDays = "care_month.number_of_days_with_remote_measurements < 16";
        $c_gte16MeasurementDays = "care_month.number_of_days_with_remote_measurements >= 16";
        $c_subscribedToSMS = "client.send_sms_on_bdt_measurement = TRUE";
        $c_deviceUsed = "(client.most_recent_cellular_bp_measurement_at IS NOT NULL OR most_recent_cellular_weight_measurement_at IS NOT NULL)";
        $c_lt20BillingMinutes = "care_month.rm_total_time_in_seconds_by_mcp < 1200";
        $c_gte20BillingMinutes = "care_month.rm_total_time_in_seconds_by_mcp >= 1200";

        switch ($rc) {
            case 1:
                $conditions = [$c_isMCP];
                break;
            case 2:
                $conditions = [$c_isMCP, $c_enrolledInRPM];
                break;
            case 3:
                $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice];
                break;
            case 4:
                $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, ($rc2 == 1 ? $c_lastVisitBefore90Days : $c_lastVisitWithin90Days)];
                break;
            case 5:
                $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, ($rc2 == 1 ? $c_notSpokenToThisMonth : $c_spokenToThisMonth)];
                break;
            case 6:
                $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, ($rc2 == 1 ? $c_hasUnstamped : $c_hasNoUnstamped)];
                break;
            case 7:
                $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, ($rc2 == 1 ? $c_lt16MeasurementDays : $c_gte16MeasurementDays)];
                break;
            case 10:
                $conditions = [$c_isMCP, $c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, $c_gte16MeasurementDays, ($rc2 == 1 ? $c_lt20BillingMinutes : $c_gte20BillingMinutes)];
                break;
            case 8:
                $conditions = [$c_isMCP, $c_enrolledInRPM, $c_subscribedToSMS];
                break;
            case 9:
                $conditions = [$c_isMCP, $c_enrolledInRPM, $c_deviceUsed];
                break;
        }

        return $conditions;
    }

    private function rpmConditionsAdmin($performer, $rc, $rc2) {
        $conditions = [];

        $c_enrolledInRPM = "client.is_enrolled_in_rm = 'YES'";
        $c_hasDevice = "(SELECT COUNT(client_bdt_device.id) FROM client_bdt_device JOIN bdt_device bd on client_bdt_device.device_id = bd.id WHERE client_bdt_device.client_id = client.id) > 0";
        $c_lastVisitBefore90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) > 90";
        $c_lastVisitWithin90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) <= 90";
        $c_notSpokenToThisMonth = "(care_month.has_anyone_interacted_with_client_about_rm_outside_note IS NULL OR care_month.has_anyone_interacted_with_client_about_rm_outside_note = FALSE)";
        $c_spokenToThisMonth = "care_month.has_anyone_interacted_with_client_about_rm_outside_note = TRUE";
        $c_hasUnstamped = "care_month.rm_num_measurements_not_stamped_by_mcp > 0";
        $c_hasNoUnstamped = "care_month.rm_num_measurements_not_stamped_by_mcp = 0";
        $c_lt16MeasurementDays = "care_month.number_of_days_with_remote_measurements < 16";
        $c_gte16MeasurementDays = "care_month.number_of_days_with_remote_measurements >= 16";
        $c_subscribedToSMS = "client.send_sms_on_bdt_measurement = TRUE";
        $c_deviceUsed = "(client.most_recent_cellular_bp_measurement_at IS NOT NULL OR most_recent_cellular_weight_measurement_at IS NOT NULL)";
        $c_lt20BillingMinutes = "care_month.rm_total_time_in_seconds_by_mcp < 1200";
        $c_gte20BillingMinutes = "care_month.rm_total_time_in_seconds_by_mcp >= 1200";

        switch ($rc) {
            case 2:
                $conditions = [$c_enrolledInRPM];
                break;
            case 3:
                $conditions = [$c_enrolledInRPM, $c_hasDevice];
                break;
            case 4:
                $conditions = [$c_enrolledInRPM, $c_hasDevice, ($rc2 == 1 ? $c_lastVisitBefore90Days : $c_lastVisitWithin90Days)];
                break;
            case 5:
                $conditions = [$c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, ($rc2 == 1 ? $c_notSpokenToThisMonth : $c_spokenToThisMonth)];
                break;
            case 6:
                $conditions = [$c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, ($rc2 == 1 ? $c_hasUnstamped : $c_hasNoUnstamped)];
                break;
            case 7:
                $conditions = [$c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, ($rc2 == 1 ? $c_lt16MeasurementDays : $c_gte16MeasurementDays)];
                break;
            case 10:
                $conditions = [$c_enrolledInRPM, $c_hasDevice, $c_lastVisitWithin90Days, $c_spokenToThisMonth, $c_gte16MeasurementDays, ($rc2 == 1 ? $c_lt20BillingMinutes : $c_gte20BillingMinutes)];
                break;
            case 8:
                $conditions = [$c_enrolledInRPM, $c_subscribedToSMS];
                break;
            case 9:
                $conditions = [$c_enrolledInRPM, $c_deviceUsed];
                break;
        }

        return $conditions;
    }

    public function remoteMonitoringMeasurements(Request $request, CareMonth $careMonth) {

        $performer = $this->performer();

        $measurements = DB::select(
            DB::raw(
                "
SELECT measurement.label,
    measurement.ts,
    measurement.effective_date,
    measurement.sbp_mm_hg,
    measurement.dbp_mm_hg,
    measurement.value_pulse,
    measurement.value_irregular,
    measurement.numeric_value,
    measurement.value,
    measurement.uid,
    client.name_first,
    client.name_last,
    client.mcp_pro_id,
    client.default_na_pro_id,
    client.rmm_pro_id,
    client.rme_pro_id
FROM measurement RIGHT JOIN client on measurement.client_id = client.id
WHERE
    client.id = {$careMonth->client_id}
    AND measurement.label IS NOT NULL
    AND measurement.label NOT IN ('SBP', 'DBP')
    AND (measurement.is_cellular_zero = FALSE or measurement.is_cellular_zero IS NULL)
    AND measurement.is_removed IS FALSE
    AND measurement.client_bdt_measurement_id IS NOT NULL
    AND measurement.care_month_id = {$careMonth->id}
    AND (
        (client.mcp_pro_id = {$performer->pro->id} AND measurement.has_been_stamped_by_mcp IS FALSE)
        OR (client.rmm_pro_id = {$performer->pro->id} AND measurement.has_been_stamped_by_rmm IS FALSE)
        OR (client.rme_pro_id = {$performer->pro->id} AND measurement.has_been_stamped_by_rme IS FALSE)
        OR (client.default_na_pro_id = {$performer->pro->id} AND measurement.has_been_stamped_by_non_hcp IS FALSE)
    )
ORDER BY ts DESC
"
            )
        );

        return view('app.practice-management.remote-monitoring-measurements', compact('careMonth', 'measurements'));
    }

    public function billMatrix(Request $request)
    {
        $bClients = [];
        $bHCPPros = [];
        $bNAPros = [];

        $filters = [];
        $filters['client'] = $request->input('client');
        $filters['service'] = $request->input('service');
        $filters['hcp'] = $request->input('hcp');
        $filters['hcp_paid'] = $request->input('hcp_paid');
        $filters['expected_op'] = $request->input('expected_op');
        $filters['expected_value'] = $request->input('expected_value');
        $filters['paid_op'] = $request->input('paid_op');
        $filters['paid_value'] = $request->input('paid_value');
        $filters['bal_post_date_op'] = $request->input('bal_post_date_op');
        $filters['bal_post_date_value'] = $request->input('bal_post_date_value');
        $filters['hcp_sign'] = $request->input('hcp_sign');
        $filters['verified'] = $request->input('verified');
        $filters['cancelled'] = $request->input('cancelled');

        $bills = Bill::orderBy('effective_date')->paginate();
        return view('app.practice-management.bill-matrix', compact('bills', 'bClients', 'bHCPPros', 'filters'));
    }

    public function medicarePartBClaims(Request $request)
    {

        $medicarePartBOnly = $request->get("medicare_part_b");

        $allClaims = Claim::where('was_submitted', false)->orWhere('was_submitted', null)->orderBy('created_at', 'desc')->get();

        //Only medicare claims
        $claims = [];
        foreach ($allClaims as $claim) {
            if ($claim->client != null && $claim->client->getPrimaryCoverageStatus() == 'YES' && !$claim->edi) {
                $claims[] = $claim;
            }
        }

        $claimEDIs = ClaimEDI::all();

        return view('app.practice-management.medicare-partb-claims', compact('claims', 'claimEDIs'));
    }

    // Generate PDF
    public function downloadClaims()
    {
        $claims = Claim::where('was_submitted', false)->orWhere('was_submitted', null)->orderBy('created_at', 'desc')->limit(100)->get();
        view()->share('claims', $claims);

        $pdf = PDF::loadView('app.practice-management.claims-pdf', $claims);

        return $pdf->download('pdf_file.pdf');
    }

    public function tickets(Request $request, $proUid = null)
    {
        $tickets = Ticket::orderBy('created_at', 'desc')->paginate();
        return view('app.practice-management.tickets', compact('tickets'));
    }

    public function supplyOrders(Request $request)
    {

        // counts
        $counts = $this->getSupplyOrderCounts();

        // so clients
        $soClientIDs = DB::table('supply_order')->select('client_id')->distinct()->get()->toArray();
        $soClientIDs = array_map(function ($_x) {
            return $_x->client_id;
        }, $soClientIDs);
        $soClients = Client::whereIn('id', $soClientIDs)->get();

        // so products
        $soProductIDs = DB::table('supply_order')->select('product_id')->distinct()->get()->toArray();
        $soProductIDs = array_map(function ($_x) {
            return $_x->product_id;
        }, $soProductIDs);
        $soProducts = Product::whereIn('id', $soProductIDs)->get();

        $filters = [];
        $filters['client'] = $request->input('client');
        $filters['product'] = $request->input('product');
        $filters['reason'] = $request->input('reason');
        $filters['cu_memo'] = $request->input('cu_memo');
        $filters['pro_sign'] = $request->input('pro_sign');
        $filters['client_sign'] = $request->input('client_sign');
        $filters['shipment'] = $request->input('shipment');
        $filters['lot_number'] = $request->input('lot_number');
        $filters['imei'] = $request->input('imei');
        $filters['cancelled'] = $request->input('cancelled');

        $supplyOrders = SupplyOrder::where('id', '>', 0);

        // apply filters
        if ($filters['client']) $supplyOrders->where('client_id', $filters['client']);
        if ($filters['product']) $supplyOrders->where('product_id', $filters['product']);
        if ($filters['reason']) $supplyOrders->where('reason', 'ILIKE', '%' . $filters['reason'] . '%');
        if ($filters['cu_memo']) $supplyOrders->where('cu_memo', 'ILIKE', '%' . $filters['cu_memo'] . '%');
        if ($filters['pro_sign']) $supplyOrders->where('is_signed_by_pro', ($filters['pro_sign'] === 'signed'));

        if ($filters['client_sign']) {
            if ($filters['client_sign'] === 'signed')
                $supplyOrders->where('is_signed_by_client', true);
            elseif ($filters['client_sign'] === 'waived')
                $supplyOrders->where('is_client_signature_waived', true);
            else
                $supplyOrders->where('is_client_signature_waived', false)->where('is_signed_by_client', false);
        }

        if ($filters['shipment']) {
            if ($filters['shipment'] === 'not_cleared_for_shipment')
                $supplyOrders->whereNull('shipment_id')->where('is_cleared_for_shipment', false);
            elseif ($filters['shipment'] === 'cleared_for_shipment')
                $supplyOrders->whereNull('shipment_id')->where('is_cleared_for_shipment', true);
            else
                $supplyOrders
                    ->whereNotNull('shipment_id')
                    ->whereRaw('(SELECT status FROM shipment WHERE id = shipment_id LIMIT 1) = ?', [$filters['shipment']]);
        }

        if ($filters['lot_number']) $supplyOrders->where('lot_number', 'ILIKE', '%' . $filters['lot_number'] . '%');
        if ($filters['imei']) $supplyOrders->where('imei', 'ILIKE', '%' . $filters['imei'] . '%');
        if ($filters['cancelled']) $supplyOrders->where('is_cancelled', ($filters['cancelled'] === 'cancelled'));

        $supplyOrders = $supplyOrders->orderBy('created_at', 'desc')->paginate();

        return view('app.practice-management.supply-orders',
            compact('supplyOrders', 'filters',
                'soClients', 'soProducts', 'counts'
            )
        );
    }

    public function shipments(Request $request, $filter = null)
    {

        // counts
        $counts = $this->getShipmentCounts();

        // so clients
        $shClientIDs = DB::table('shipment')->select('client_id')->distinct()->get()->toArray();
        $shClientIDs = array_map(function ($_x) {
            return $_x->client_id;
        }, $shClientIDs);
        $shClients = Client::whereIn('id', $shClientIDs)->get();

        $shipments = Shipment::where('id', '>', 0);

        $filters = [];
        $filters['client'] = $request->input('client');
        $filters['courier'] = $request->input('courier');
        $filters['tracking_num'] = $request->input('tracking_num');
        $filters['label'] = $request->input('label');
        $filters['status'] = $request->input('status');
        $filters['cancelled'] = $request->input('cancelled');

        if ($filters['client']) $shipments->where('client_id', $filters['client']);
        if ($filters['courier']) $shipments->where('courier', 'ILIKE', '%' . $filters['courier'] . '%');
        if ($filters['tracking_num']) $shipments->where('tracking_number', 'ILIKE', '%' . $filters['tracking_num'] . '%');
        if ($filters['label']) {
            if ($filters['label'] === 'yes')
                $shipments->whereNotNull('label_system_file_id');
            else
                $shipments->whereNull('label_system_file_id');
        }
        if ($filters['status']) $shipments->where('status', $filters['status']);
        if ($filters['cancelled']) $shipments->where('is_cancelled', ($filters['cancelled'] === 'cancelled'));

        $shipments = $shipments->orderBy('created_at', 'desc')->paginate();
        return view('app.practice-management.shipments', compact('shipments', 'filters', 'shClients', 'counts'));
    }

    public function cellularMeasurements(Request $request)
    {
        $measurements = Measurement::orderBy('ts', 'desc')->whereNotNull('ts')->paginate();
        return view('app.practice-management.cellular-measurements', compact('measurements'));
    }

    // v2 supply-orders & shipments management (wh)

    public function supplyOrdersReadyToShip(Request $request)
    {
        $counts = $this->getSupplyOrderCounts();
        $supplyOrders = SupplyOrder
            ::where('is_cleared_for_shipment', true)
            ->where('is_cancelled', false)
            ->whereNull('shipment_id')
            ->join('client', 'client.id', '=', 'supply_order.client_id')
            ->orderBy('client.name_last', 'ASC')
            ->orderBy('client.name_first', 'ASC')
            ->orderBy('supply_order.client_id', 'ASC')
            ->orderBy('supply_order.mailing_address_full', 'ASC')
            ->orderBy('supply_order.created_at', 'ASC')
            ->select('supply_order.*')
            ->paginate();
        return view('app.practice-management.supply-orders-ready-to-ship', compact('supplyOrders', 'counts'));
    }

    public function supplyOrdersShipmentUnderway(Request $request)
    {
        $counts = $this->getSupplyOrderCounts();
        $supplyOrders = SupplyOrder
            ::where('is_cancelled', false)
            ->whereNotNull('shipment_id')
            ->orderBy('client_id', 'ASC')
            ->orderBy('mailing_address_full', 'ASC')
            ->orderBy('created_at', 'ASC')
            ->paginate();
        return view('app.practice-management.supply-orders-shipment-underway', compact('supplyOrders', 'counts'));
    }

    public function supplyOrdersHanging(Request $request)
    {
        $counts = $this->getSupplyOrderCounts();
        $supplyOrders = SupplyOrder
            ::select('supply_order.*')
            ->leftJoin('shipment', function($join) {
                $join->on('supply_order.shipment_id', '=', 'shipment.id');
            })
            ->where('shipment.status', 'CANCELLED')
            ->where('supply_order.is_cancelled', false)
            ->orderBy('supply_order.client_id', 'ASC')
            ->orderBy('supply_order.mailing_address_full', 'ASC')
            ->orderBy('supply_order.created_at', 'ASC')
            ->paginate();
        return view('app.practice-management.supply-orders-hanging', compact('supplyOrders', 'counts'));
    }

    public function supplyOrdersCancelledButUnacknowledged(Request $request)
    {
        $supplyOrders = SupplyOrder::where('signed_by_pro_id', $this->performer()->pro->id)
            ->where('is_cancelled', true)
            ->where('is_cancellation_acknowledged', false)
	        ->orderBy('created_at', 'desc')
            ->paginate();
        return view('app.practice-management.supply-orders-cancelled-but-unacknowledged', compact('supplyOrders'));
    }

    public function supplyOrdersUnsigned(Request $request)
    {
        $supplyOrders = SupplyOrder
            ::where('is_cancelled', false)
            ->where('is_signed_by_pro', false)
            ->whereRaw('created_by_session_id IN (SELECT id FROM app_session WHERE pro_id = ?)', [$this->performer()->pro->id])
            ->orderBy('created_at', 'desc')
            ->paginate();
        return view('app.practice-management.supply-orders-unsigned', compact('supplyOrders'));
    }

    private function getSupplyOrderCounts()
    {
        return [
            "supplyOrders" => SupplyOrder::count(),
            "supplyOrdersReadyToShip" => SupplyOrder
                ::where('is_cleared_for_shipment', true)
                ->where('is_cancelled', false)
                ->whereNull('shipment_id')->count(),
            "supplyOrdersShipmentUnderway" => SupplyOrder
                ::where('is_cancelled', false)
                ->whereNotNull('shipment_id')->count(),
            "supplyOrdersHanging" => SupplyOrder
                ::leftJoin('shipment', function($join) {
                    $join->on('supply_order.shipment_id', '=', 'shipment.id');
                })
                ->where('shipment.status', 'CANCELLED')
                ->where('supply_order.is_cancelled', false)
                ->count(),
        ];
    }

    public function shipmentsReadyToPrint(Request $request)
    {
        $counts = $this->getShipmentCounts();
        $shipments = Shipment
            ::where('is_cancelled', false)
            ->where('status', 'CREATED')
            ->orderBy('created_at', 'ASC')
            ->paginate();
        return view('app.practice-management.shipments-ready-to-print', compact('shipments', 'counts'));
    }

    public function shipmentsShipmentUnderway(Request $request)
    {
        $counts = $this->getShipmentCounts();
        $shipments = Shipment
            ::where('is_cancelled', false)
            ->where('status', 'PRINTED')
            ->orderBy('created_at', 'ASC')
            ->paginate();
        return view('app.practice-management.shipments-waiting-for-picker', compact('shipments', 'counts'));
    }

    private function getShipmentCounts()
    {
        return [
            "shipments" => Shipment::count(),
            "shipmentsReadyToPrint" => Shipment
                ::where('is_cancelled', false)
                ->where('status', 'CREATED')
                ->count(),
            "shipmentsWaitingForPicker" => Shipment
                ::where('is_cancelled', false)
                ->where('status', 'PRINTED')
                ->count()
        ];
    }

    public function shipment(Request $request, Shipment $shipment)
    {
        return view('app.practice-management.shipment', compact('shipment'));
    }

    public function shipmentsMultiPrint(Request $request, $ids)
    {
        $ids = array_map(function ($_x) {
            return intval($_x);
        }, explode("|", $ids));
        $shipments = Shipment::whereIn('id', $ids)->get();
        return view('app.practice-management.shipments-multi-print', compact('shipments'));
    }

    public function patientClaimSummary(Request $request, $proUid = null)
    {

        $notesTotal = DB::select(DB::raw("SELECT COUNT(*) FROM note WHERE is_cancelled IS NOT TRUE"))[0]->count;
        $notesTotalWithBillingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM note WHERE is_cancelled IS NOT TRUE AND is_bill_closed IS TRUE"))[0]->count;
        $notesTotalWithClaimingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM note WHERE is_cancelled IS NOT TRUE AND is_claim_closed IS TRUE"))[0]->count;

        $notes3rdPartyTotal = DB::select(DB::raw("SELECT COUNT(*) FROM note n LEFT JOIN client c ON n.client_id = c.id WHERE n.is_cancelled IS NOT TRUE AND c.is_part_b_primary <> 'YES'"))[0]->count;
        $notes3rdPartyTotalWithBillingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM note n LEFT JOIN client c ON n.client_id = c.id WHERE n.is_cancelled IS NOT TRUE AND n.is_bill_closed IS TRUE AND c.is_part_b_primary <> 'YES'"))[0]->count;
        $notes3rdPartyTotalWithClaimingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM note n LEFT JOIN client c ON n.client_id = c.id WHERE n.is_cancelled IS NOT TRUE AND n.is_claim_closed IS TRUE AND c.is_part_b_primary <> 'YES'"))[0]->count;

        $patientsTotal = DB::select(DB::raw("SELECT COUNT(*) FROM client WHERE is_active IS TRUE AND 0 NOT IN (SELECT c FROM (SELECT COUNT(*) c FROM note WHERE is_cancelled IS NOT TRUE AND note.client_id = client.id) x)"))[0]->count;
        $patientsTotalWithBillingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM client WHERE is_active IS TRUE AND 0 NOT IN (SELECT c FROM (SELECT COUNT(*) c FROM note WHERE is_cancelled IS NOT TRUE AND note.client_id = client.id) y) AND 0 IN (SELECT c FROM (SELECT COUNT(*) c FROM note WHERE is_cancelled IS NOT TRUE AND is_bill_closed IS NOT TRUE AND note.client_id = client.id) x)"))[0]->count;
        $patientsTotalWithClaimingClosed = DB::select(DB::raw("SELECT COUNT(*) FROM client WHERE is_active IS TRUE AND 0 NOT IN (SELECT c FROM (SELECT COUNT(*) c FROM note WHERE is_cancelled IS NOT TRUE AND note.client_id = client.id) y) AND 0 IN (SELECT c FROM (SELECT COUNT(*) c FROM note WHERE is_cancelled IS NOT TRUE AND is_claim_closed IS NOT TRUE AND note.client_id = client.id) x)"))[0]->count;


        $performerPro = $this->performer->pro;
        $allPros = [];
        if ($performerPro->pro_type == 'ADMIN') {
            $allPros = Pro::all();
        } else {
            $allPros = [$performerPro];
        }

        //Patient | MCP | # Notes Total | # Notes without Billing Closed | # Notes without Claiming Closed
        $patientsQuery = Client::where('is_dummy', '=', false)
            ->whereNull('shadow_pro_id')
            ->select('id', 'uid', 'name_first', 'name_last', 'mcp_pro_id', 'is_part_b_primary', 'medicare_advantage_plan',
                DB::raw("(SELECT name_first||' '||name_last FROM pro where pro.id = client.mcp_pro_id) as mcp"),
                DB::raw("(SELECT uid FROM pro where pro.id = mcp_pro_id) as mcp_pro_uid"),
                DB::raw("(SELECT COUNT(*) FROM note where note.client_id = client.id) as notes_total"),
                DB::raw("(SELECT COUNT(*) FROM note where note.client_id = client.id AND is_bill_closed IS NOT true) as notes_without_billing_closed"),
                DB::raw("(SELECT COUNT(*) FROM note where note.client_id = client.id AND is_claim_closed IS NOT true) as notes_without_claiming_closed")
            )->orderBy('is_part_b_primary', 'asc')->orderBy('notes_without_claiming_closed', 'desc');

        if ($proUid) {
            $mcpPro = Pro::where('uid', $proUid)->first();
            if ($mcpPro) {
                $patientsQuery->where('client.mcp_pro_id', '=', $mcpPro->id);
            }
        }

        $patientsQuery->whereRaw('(SELECT COUNT(*) FROM note where note.client_id = client.id) > 0');

        $patientsQuery->orderBy('notes_total', 'DESC');

        $patients = $patientsQuery->paginate(50);

        $data = [
            'patients' => $patients,
            'proUid' => $proUid,
            'allPros' => $allPros,
            'notesTotal' => $notesTotal,
            'notesTotalWithBillingClosed' => $notesTotalWithBillingClosed,
            'notesTotalWithClaimingClosed' => $notesTotalWithClaimingClosed,

            'notes3rdPartyTotal' => $notes3rdPartyTotal,
            'notes3rdPartyTotalWithBillingClosed' => $notes3rdPartyTotalWithBillingClosed,
            'notes3rdPartyTotalWithClaimingClosed' => $notes3rdPartyTotalWithClaimingClosed,

            'patientsTotal' => $patientsTotal,
            'patientsTotalWithBillingClosed' => $patientsTotalWithBillingClosed,
            'patientsTotalWithClaimingClosed' => $patientsTotalWithClaimingClosed

        ];

        return view('app.practice-management.patient-claim-summary', $data);
    }

    public function claims(Request  $request){
        $status = $request->get('status');
        $claims = [];
        if(!$status){
            $claims = Claim::orderBy('created_at', 'DESC')->paginate();
        }else{
            $claims = Claim::where('status', $status)->orderBy('created_at', 'DESC')->paginate();
        }
        return  view('app.practice-management.claims', compact('claims', 'status'));
    }

    public function processClaims(Request  $request) {
        $status = '';

        $q = $request->input('q') ? $request->input('q') : '';
        $from = $request->input('from') ? $request->input('from') : '1900-01-01';
        $to = $request->input('to') ? $request->input('to') : '2100-01-01';

        $params = [
            'q' => '%' . $q . '%',
            'from' => $from,
            'to' => $to
        ];

        $hcpPro = $request->input('hcp') ? Pro::where('uid', $request->input('hcp'))->first() : null;
        if($hcpPro) {
            $params['hcp'] = $hcpPro->id;
        }

        $claims = DB::select(DB::raw("
SELECT claim.uid AS uid,
       DATE(claim.created_at) AS created,
       claim.status,
       client.uid AS client_uid,
       client.cell_number AS client_phone,
       client.phone_home AS client_phone_home,
       (client.name_last || ' ' || client.name_first) AS client ,
       client.chart_number AS client_chart_number,
       cp.id AS claim_pro_id,
       (cp.name_last || ' ' || cp.name_first) AS claim_pro,
       sp.id AS status_pro_id,
       (sp.name_last || ' ' || sp.name_first) AS status_pro,
       note.uid AS note_uid,
       note.method,
       note.new_or_fu_or_na,
       care_month.uid AS care_month_uid,
       -- claim.status_updated_at,
       (DATE(claim.status_updated_at) || ' ' ||
            LPAD(EXTRACT(hour FROM claim.status_updated_at)::text, 2, '0') || ':' ||
            LPAD(EXTRACT(minute FROM claim.status_updated_at)::text, 2, '0')) AS status_updated_at,
       (SELECT string_agg(claim_line.cpt, ', ') FROM claim_line where claim_id = claim.id) AS cpts,
       (SELECT COUNT(claim_line_icd.id) FROM claim_line_icd WHERE claim_line_id IN (SELECT id FROM claim_line WHERE claim_id = claim.id)) AS icds,
       ROUND(claim.expected_total, 3) as expected_total
FROM claim
    join client on claim.client_id = client.id
    join pro cp on claim.pro_id = cp.id
    left join note on claim.note_id = note.id
    left join care_month on claim.care_month_id = care_month.id
    left join app_session on claim.status_updated_by_session_id = app_session.id
    left join pro sp on app_session.pro_id = sp.id
--WHERE claim.status IS NULL OR claim.status = 'NEW'
WHERE (claim.status is NULL OR claim.status NOT IN ('CANCELLED', 'ABANDONED'" . ($request->input('show-submitted') ? "" : ", 'SUBMITTED'") . "))
-- AND claim.current_version_id IS NOT NULL
AND (client.name_first ILIKE :q OR
     client.name_last ILIKE :q OR
     client.chart_number ILIKE :q OR
     client.mcn ILIKE :q)
AND (claim.created_at >= :from AND claim.created_at <= :to)
" . ($hcpPro ? "AND claim.pro_id = :hcp" : '') . "
AND claim.id IN (SELECT mb_claim.claim_id FROM mb_claim)
ORDER BY claim.created_at DESC
--OFFSET 0 LIMIT 15
"), $params);

        if($request->input('json')) {
            return json_encode($claims);
        }

        return  view('app.practice-management.process-claims', compact('claims', 'status'));
    }

    public function rmLaunchAndClean(Request  $request) {

        $keyNumbers = [];

        $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP_PLUS_40'] = CareMonth::where('is_bill_closed', false)
            ->whereNotNull('company_pro_id')
            ->whereNotNull('company_pro_payer_id')
            ->whereNotNull('company_location_id')
            ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
            ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
            ->where('rm_total_time_in_seconds', '>=', 4200) // at 4200 (70 minutes, it becomes eligible for plus40)
            ->where('number_of_days_with_remote_measurements', '>=', 16 )
            ->whereRaw('is_rm_time_waived IS NOT TRUE')
            ->whereRaw('(has_anyone_interacted_with_client_about_rm_outside_note IS  TRUE OR has_mcp_rm_interacted_by_note IS  TRUE )')
            ->count();

        $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP_PLUS_40_ifHadInteraction'] = CareMonth::where('is_bill_closed', false)
            ->whereNotNull('company_pro_id')
            ->whereNotNull('company_pro_payer_id')
            ->whereNotNull('company_location_id')
            ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
            ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
            ->where('rm_total_time_in_seconds', '>=', 4200) // at 4200 (70 minutes, it becomes eligible for plus40)
            ->where('number_of_days_with_remote_measurements', '>=', 16 )
            ->whereRaw('is_rm_time_waived IS NOT TRUE')
            ->whereRaw('(has_anyone_interacted_with_client_about_rm_outside_note IS NOT TRUE AND has_mcp_rm_interacted_by_note IS NOT TRUE AND is_rm_interaction_waived IS NOT TRUE )')
            ->count();

        $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP_PLUS_20'] = CareMonth::where('is_bill_closed', false)
            ->whereNotNull('company_pro_id')
            ->whereNotNull('company_pro_payer_id')
            ->whereNotNull('company_location_id')
            ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
            ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
            ->where('rm_total_time_in_seconds', '<', 4200)
            ->where('rm_total_time_in_seconds', '>=', 3000) // at 3000 (50 minutes, it becomes eligible for plus20)
            ->where('number_of_days_with_remote_measurements', '>=', 16 )
            ->whereRaw('is_rm_time_waived IS NOT TRUE')
            ->whereRaw('(has_anyone_interacted_with_client_about_rm_outside_note IS TRUE OR has_mcp_rm_interacted_by_note IS TRUE)')
            ->count();

        $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP_PLUS_20_ifHadInteraction'] = CareMonth::where('is_bill_closed', false)
            ->whereNotNull('company_pro_id')
            ->whereNotNull('company_pro_payer_id')
            ->whereNotNull('company_location_id')
            ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
            ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
            ->where('rm_total_time_in_seconds', '<', 4200)
            ->where('rm_total_time_in_seconds', '>=', 3000) // at 3000 (50 minutes, it becomes eligible for plus20)
            ->where('number_of_days_with_remote_measurements', '>=', 16 )
            ->whereRaw('is_rm_time_waived IS NOT TRUE')
            ->whereRaw('(has_anyone_interacted_with_client_about_rm_outside_note IS NOT TRUE AND has_mcp_rm_interacted_by_note IS NOT TRUE AND is_rm_interaction_waived IS NOT TRUE )')
            ->count();

        $keyNumbers['careMonthsEligibleForBillGeneration_RM30_HCP'] = CareMonth::where('is_bill_closed', false)
            ->whereNotNull('company_pro_id')
            ->whereNotNull('company_pro_payer_id')
            ->whereNotNull('company_location_id')
            ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
            ->where('rm_total_time_in_seconds_by_mcp', '>=', 1800)
            ->whereRaw('is_rm_time_waived IS NOT TRUE')
            ->whereRaw('(rm_total_time_in_seconds < 3000 OR (rm_total_time_in_seconds >= 3000 AND is_rm_interaction_waived IS TRUE))')
            ->where('number_of_days_with_remote_measurements', '>=', 16 )
            ->count();

        $keyNumbers['careMonthsEligibleForBillGeneration_RMB'] = CareMonth::where('is_bill_closed', false)
            ->whereNotNull('company_pro_id')
            ->whereNotNull('company_pro_payer_id')
            ->whereNotNull('company_location_id')
            ->whereRaw('(SELECT count(id) FROM care_month_cm_rm_reason WHERE care_month_id = care_month.id) > 0')
            ->whereRaw('is_rm_time_waived IS TRUE')
            ->where('number_of_days_with_remote_measurements', '>=', 16 )
            ->count();

        $keyNumbers['careMonthsWith16OrMoreMeasurementDays'] = CareMonth::where('is_bill_closed', false)
            ->where('number_of_days_with_remote_measurements', '>=', 16 )
            ->count();

        $careMonthsWith16PlusMeasurements = CareMonth::where('is_bill_closed', false)
            ->where('number_of_days_with_remote_measurements', '>=', 16 )->get();


        return  view('app.practice-management.rm-launch-and-clean', compact('keyNumbers', 'careMonthsWith16PlusMeasurements'));
    }

    public function processNotes(Request  $request) {

        $mode = $request->input('mode') ? $request->input('mode') : '1';
        if(($mode < 1 || $mode > 5) && $mode != 8) $mode = 1;

	DB::enableQueryLog(); // Enable query log

	$test = Note::where('is_cancelled', false)
                ->where('is_signed_by_hcp', true)
                ->whereRaw("((detail_json)::json->>'isGood')::text = 'true'")
                ->whereNull('current_note_pickup_for_processing_id')
                ->where('is_billing_marked_done', true)
                ->where('is_bill_closed', true)
                ->where('is_claim_closed', true)
                ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0)")
                ->count();

	// var_dump($test);

	// dd(DB::getQueryLog()); // Show results of log

	// exit;

        $counts = [
            "picked" => Note::where('is_cancelled', false)
                ->whereNotNull('current_note_pickup_for_processing_id')
                ->count(),
            "bad" => Note::where('is_cancelled', false)
                ->whereRaw("((detail_json)::json->>'isBad' = 'true')")
                ->count(),
            // not yet signed
            "mode-1" => Note::where('is_cancelled', false)
                ->where('is_signed_by_hcp', false)
                ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
//                ->whereNull('current_note_pickup_for_processing_id')
                ->count(),
            // billing not marked done
            "mode-2" => Note::where('is_cancelled', false)
                ->where('is_signed_by_hcp', true)
                //->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
//                ->whereNull('current_note_pickup_for_processing_id')
                ->where('is_billing_marked_done', false)
                ->count(),
            // billing not closed
            "mode-3" => Note::where('is_cancelled', false)
                ->where('is_signed_by_hcp', true)
                ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
//                ->whereNull('current_note_pickup_for_processing_id')
                ->where('is_billing_marked_done', true)
                ->where('is_bill_closed', false)
                ->count(),
            // claiming not closed
            "mode-4" => Note::where('is_cancelled', false)
                ->where('is_signed_by_hcp', true)
                ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
//                ->whereNull('current_note_pickup_for_processing_id')
                ->whereRaw("(((detail_json)::json->>'isGood')::text <> 'true' OR ((detail_json)::json->>'isGood')::text IS NULL)")
                ->where('is_billing_marked_done', true)
                ->where('is_bill_closed', true)
                ->where('is_claim_closed', false)
                ->count(),
            // has unsubmitted claims
            "mode-5" => Note::where('is_cancelled', false)

		/*->where('is_signed_by_hcp', true)
                ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
                ->whereNull('current_note_pickup_for_processing_id')
                ->where('is_billing_marked_done', true)
                ->where('is_bill_closed', true)
                ->where('is_claim_closed', true)
                ->whereRaw("(SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') > 0")
*/

                ->where('is_signed_by_hcp', true)
                ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
//                ->whereNull('current_note_pickup_for_processing_id')
                ->where('is_billing_marked_done', true)
                ->where('is_bill_closed', true)
                // ->whereRaw("(((detail_json)::json->>'isGood')::text <> 'true')")
                ->where('is_claim_closed', true)
                ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') > 0)")

		->count(),
 // has unsubmitted claims MARKED GOOD!
            "mode-8" => Note::where('is_cancelled', false)
                ->where('is_signed_by_hcp', true)
                ->whereRaw("((detail_json)::json->>'isGood')::text = 'true'")
                ->whereNull('current_note_pickup_for_processing_id')
                ->where('is_billing_marked_done', true)
                ->where('is_bill_closed', true)
                ->where('is_claim_closed', false)
                ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0)")
		->count(),
            // all good
            "mode-6" => Note::where('is_cancelled', false)
                ->where('is_signed_by_hcp', true)
                ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
//                ->whereNull('current_note_pickup_for_processing_id')
                ->where('is_billing_marked_done', true)
                ->where('is_bill_closed', true)
                ->where('is_claim_closed', true)
                ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id) > 0)")
                ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0)")
                ->count(),
            "mode-7" => Note::where('is_cancelled', false)
                ->whereRaw("(detail_json)::json->>'isBad' = 'true'")
                ->count(),
        ];

        return  view('app.practice-management.process-notes', compact('mode', 'counts'));
    }

    public function notesProcessingCenter(Request  $request) {

        $notes = Note::where('is_cancelled', false);

        // filters
        $proUid = $request->input('proUid');
        if($proUid) {
            $fPro = Pro::where('uid', $proUid)->first();
            if($fPro) {
                $notes = $notes->where('hcp_pro_id', $fPro->id);
            }
        }
        $proSigned = $request->input('proSigned');
        switch($proSigned) {
            case 'yes': $notes = $notes->where('is_signed_by_hcp', true); break;
            case 'no': $notes = $notes->where('is_signed_by_hcp', '!=', true); break;
        }
        $billingMarkedDone = $request->input('billingMarkedDone');
        switch($billingMarkedDone) {
            case 'yes': $notes = $notes->where('is_billing_marked_done', true); break;
            case 'no': $notes = $notes->where('is_billing_marked_done', '!=', true); break;
        }
        $billingClosed = $request->input('billingClosed');
        switch($billingClosed) {
            case 'yes': $notes = $notes->where('is_bill_closed', true); break;
            case 'no': $notes = $notes->where('is_bill_closed', '!=', true); break;
        }
        $claimingClosed = $request->input('claimingClosed');
        switch($claimingClosed) {
            case 'yes': $notes = $notes->where('is_claim_closed', true); break;
            case 'no': $notes = $notes->where('is_claim_closed', '!=', true); break;
        }
        $allClaimsSubmitted = $request->input('allClaimsSubmitted');
        if($allClaimsSubmitted) {
            // TODO
        }
        $goodBad = $request->input('goodBad');
        switch($goodBad) {
            case 'good': $notes = $notes->whereRaw("(detail_json)::json->>'isBad' = 'false'"); break;
            case 'bad': $notes = $notes->whereRaw("(detail_json)::json->>'isBad' = 'true'"); break;
            case 'unclassified': $notes = $notes->whereRaw("(detail_json)::json->>'isBad' is null"); break;
        }
        $startDate = $request->input('startDate');
        if($startDate) {
            $notes = $notes->where('effective_dateest', '>=', $startDate);
        }
        $endDate = $request->input('endDate');
        if($endDate) {
            $notes = $notes->where('effective_dateest', '<=', $endDate);
        }
        $mcPartB = $request->input('mcPartB');
        if($mcPartB) {
            // TODO
        }

        $notes = $notes->orderBy('effective_dateest')->paginate(10);

        return  view('app.practice-management.notes-processing-center', compact('notes'));
    }

    public function getNextNote(Request $request, $mode)
    {
        $note = null;
        switch (+$mode) {
            case 1:
                $note = Note::where('is_cancelled', false)
                    ->where('is_signed_by_hcp', false)
                    ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
                    ->whereNull('current_note_pickup_for_processing_id')
                    ->orderBy('effective_dateest', 'DESC')
                    ->first();
                break;
            case 2:
                $note = Note::where('is_cancelled', false)
                    ->where('is_signed_by_hcp', true)
                    ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
                    ->whereNull('current_note_pickup_for_processing_id')
                    ->where('is_billing_marked_done', false)
                    ->orderBy('effective_dateest', 'DESC')
                    ->first();
                break;
            case 3:
                $note = Note::where('is_cancelled', false)
                    ->where('is_signed_by_hcp', true)
                    ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
                    ->whereNull('current_note_pickup_for_processing_id')
                    ->where('is_billing_marked_done', true)
                    ->where('is_bill_closed', false)
                    ->orderBy('effective_dateest', 'DESC')
                    ->first();
                break;
            case 4: // claiming not closed
		DB::enableQueryLog(); // Enable query log

		$note = Note::where('is_cancelled', false)
                    ->where('is_signed_by_hcp', true)
                    ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
                    ->whereNull('current_note_pickup_for_processing_id')
                    ->where('is_billing_marked_done', true)
                    ->where('is_bill_closed', true)
                    ->whereRaw("(((detail_json)::json->>'isGood')::text <> 'true' OR ((detail_json)::json->>'isGood')::text IS NULL)")
                    ->where('is_claim_closed', false)
                    ->orderBy('effective_dateest', 'DESC')
                    ->first();

//		DB::enableQueryLog(); // Enable query log

// Your Eloquent query executed by using get()

// dd(DB::getQueryLog()); // Show results of log

                break;
            case 5:
                $note = Note::where('is_cancelled', false)
                    ->where('is_signed_by_hcp', true)
                    ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
                    ->whereNull('current_note_pickup_for_processing_id')
                    ->where('is_billing_marked_done', true)
                    ->where('is_bill_closed', true)
                    ->where('is_claim_closed', true)
                    ->whereRaw("(SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') > 0")
                    ->orderBy('effective_dateest', 'DESC')
                    ->first();
                break;
	    case 8:
		 $note = Note::where('is_cancelled', false)
                ->where('is_signed_by_hcp', true)
                ->whereRaw("((detail_json)::json->>'isGood')::text = 'true'")
                ->whereNull('current_note_pickup_for_processing_id')
                ->where('is_billing_marked_done', true)
                ->where('is_bill_closed', true)
                ->where('is_claim_closed', false)
                ->whereRaw("((SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0)")
                    ->orderBy('effective_dateest', 'DESC')
                    ->first();
                break;
        }
        if($note) {
            $note->client_uid = $note->client->uid;
        }
        return json_encode($note);
    }

    public function pickedNotes(Request  $request) {
        $counts = [
            "unpicked" => Note::where('is_cancelled', false)
                ->whereNull('current_note_pickup_for_processing_id')
                ->count(),
        ];
        $notes = Note::where('is_cancelled', false)
                ->whereNotNull('current_note_pickup_for_processing_id')
                ->orderBy('effective_dateest', 'ASC')
                ->paginate();
        return  view('app.practice-management.picked-notes', compact('counts', 'notes'));
    }

    public function badNotes(Request  $request) {
        $counts = [
            "unpicked" => Note::where('is_cancelled', false)
                ->whereNull('current_note_pickup_for_processing_id')
                ->count(),
        ];
        $notes = Note::where('is_cancelled', false)
            ->whereRaw("(detail_json)::json->>'isBad' = 'true'")
            ->orderBy('effective_dateest', 'ASC')
            ->paginate();
        return  view('app.practice-management.bad-notes', compact('counts', 'notes'));
    }

    public function doneNotes(Request  $request) {
        $counts = [
            "unpicked" => Note::where('is_cancelled', false)
                ->whereNull('current_note_pickup_for_processing_id')
                ->count(),
        ];
        $notes = Note::where('is_cancelled', false)
            ->where('is_signed_by_hcp', true)
            ->whereRaw("((detail_json)::json->>'isBad' is null OR ((detail_json)::json->>'isBad')::text != 'true')")
            ->whereNull('current_note_pickup_for_processing_id')
            ->where('is_billing_marked_done', true)
            ->where('is_bill_closed', true)
            ->where('is_claim_closed', true)
            ->whereRaw("(SELECT count(id) FROM claim WHERE note_id = note.id) > 0")
            ->whereRaw("(SELECT count(id) FROM claim WHERE note_id = note.id AND is_cancelled IS FALSE AND status != 'CANCELLED' AND status != 'SUBMITTED') = 0")
            ->orderBy('effective_dateest', 'ASC')
            ->paginate();
        return  view('app.practice-management.done-notes', compact('counts', 'notes'));
    }

    public function currentMbClaim(Request $request, $claimUid) {
        $claim = Claim::where('uid', $claimUid)->first();
        return json_encode(MBClaim::where('claim_version_id', $claim->currentVersion->id)->where('status', '!=', 'CANCELLED')->first());
    }

    public function currentClaimLines(Request $request, $claimUid) {
        $claim = Claim::where('uid', $claimUid)->first();
        if($request->input('json')) {
            foreach ($claim->lines as $line) {
                $line->expected_total = round($line->expected_total, 3);
                $x = $line->claimLineIcds;
            }
            return json_encode($claim->lines);
        }
        return  view('app.practice-management._claim-lines', compact('claim'));
    }

    public function packsMultiPrint(Request $request) {
        $packs = Pack
            ::select('pack.*')
            ->leftJoin('shipment', function($join) {
                $join->on('pack.shipment_id', '=', 'shipment.id');
            })
            ->whereNotIn('shipment.status', ['CANCELLED', 'DISPATCHED'])
            ->where(function ($query) {
                $query->where('pack.status', '<>', 'DELETED')->orWhereNull('pack.status'); // weird, but just the <> isn't working!
            })
            ->whereNotNull('pack.label_system_file_id')
            ->orderBy('pack.created_at', 'ASC')
            ->get();
        return view('app.practice-management.packs-multi-print', compact('packs'));
    }

    public function packsMultiPDF(Request $request, $ids) {
        $ids = array_map(function ($_x) {
            return intval($_x);
        }, explode("|", $ids));
        $packs = Pack::whereIn('id', $ids)->get();
    }

    public function handouts(Request $request) {
        $handouts = Handout::orderBy('display_name')->get();
        return view('app.practice-management.handouts', compact('handouts'));
    }

    private function callJava($request, $endPoint, $data)
    {
        $url =  config('stag.backendUrl') . $endPoint;
        $response = Http::asForm()
            ->withHeaders([
                'sessionKey' => $request->cookie('sessionKey')
            ])
            ->post($url, $data)
            ->body();
        dd($response);
        return $response;
    }

    public function genericBills(Request $request)
    {
        return view('app.practice-management.generic-bills');
    }

    public function billsUnderProcessing(Request $request)
    {

        $bills = Bill::where('is_cancelled', false)
            ->where(function ($query) {   // mcp of any client program and program OB pending
                $query
                    ->where(function ($_query) {
                        $_query->where('hcp_pro_id', $this->pro->id)
                            ->where('hcp_expected_payment_amount', '>', 0)
                            ->where('has_hcp_been_paid', false)
                            ->where('is_signed_by_hcp', true);
                    })
                    ->orWhere(function ($_query) {
                        $_query->where('cm_pro_id', $this->pro->id)
                            ->where('cm_expected_payment_amount', '>', 0)
                            ->where('has_cm_been_paid', false)
                            ->where('is_signed_by_cm', true);
                    })
                    ->orWhere(function ($_query) {
                        $_query->where('rme_pro_id', $this->pro->id)
                            ->where('rme_expected_payment_amount', '>', 0)
                            ->where('has_rme_been_paid', false)
                            ->where('is_signed_by_rme', true);
                    })
                    ->orWhere(function ($_query) {
                        $_query->where('rmm_pro_id', $this->pro->id)
                            ->where('rmm_expected_payment_amount', '>', 0)
                            ->where('has_rmm_been_paid', false)
                            ->where('is_signed_by_rmm', true);
                    })
                    ->orWhere(function ($_query) {
                        $_query->where('generic_pro_id', $this->pro->id)
                            ->where('generic_pro_expected_payment_amount', '>', 0)
                            ->where('has_generic_pro_been_paid', false)
                            ->where('is_signed_by_generic_pro', true);
                    });
            })
            ->orderBy('created_at', 'DESC')
            ->paginate();

        return view('app.practice-management.bills-under-processing', compact('bills'));

    }

    public function careMonthReport(Request $request) {
        $m = $request->input('m');
        $y = $request->input('y');
        if(!$m || !$y) {
            $date = date('Y-m-01');
        }
        else {
            $date = date('Y-m-d', strtotime("$m/1/$y"));
        }

        $pro = $this->pro;

        $records = DB::select("
SELECT cm.id                                as care_month_id,
       cm.uid                               as care_month_uid,
       c.id                                 as client_id,
       c.uid                                as client_uid,
       cm.number_of_days_with_remote_measurements,
       cm.rm_total_time_in_seconds_by_mcp,
       cm.rm_total_time_in_seconds_by_rme_pro,
       cm.rm_total_time_in_seconds_by_rmm_pro,
       cm.mcp_pro_id,
       cm.rme_pro_id,
       cm.rmm_pro_id,
       (c.name_first || ' ' || c.name_last) as client_name
FROM care_month cm
         JOIN client c on cm.client_id = c.id
WHERE cm.start_date = :startDate
  AND (cm.mcp_pro_id = :proID OR
       cm.rme_pro_id = :proID OR
       cm.rmm_pro_id = :proID)
ORDER BY c.name_last, c.name_first
            ",
            ['startDate' => $date, 'proID' => $pro->id]
        );

        return view('app.practice-management.care-month-report', compact('records', 'date'));
    }

    public function myTeams(Request $request) {
        $pro = $this->pro;

        // get all teams where the authed-pro is the assistant pro
        $teams = ProTeam::where('assistant_pro_id', $pro->id)
            ->where('is_active', true)
            ->orderBy('slug')
            ->get();

        return view('app.practice-management.my-teams', compact('teams'));
    }

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

        $accountInvites = AccountInvite::select('account_invite.*')
                            ->join('client', 'client.id', '=', 'account_invite.for_client_id');
        if($this->performer->pro->pro_type !== 'ADMIN'){
            $accountInvites = $accountInvites->where('client.mcp_pro_id', $this->performer->pro->id);
        }

        $this->filterMultiQuery($request, $accountInvites, 'account_invite.created_at', 'date_category', 'date_value_1', 'date_value_2');
        $this->filterSimpleQuery($request, $accountInvites, 'account_invite.status', 'status');

        $accountInvites = $accountInvites->orderBy('created_at', 'DESC')->paginate(20);

        return view('app.practice-management.patients-accounts-invites', compact('accountInvites','filters'));
    }

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

        $devices = ClientBDTDevice::select(
            'client_bdt_device.*',
            DB::raw('(SELECT COUNT(*) FROM bdt_measurement WHERE bdt_measurement.bdt_device_id = client_bdt_device.device_id AND is_cellular_zero IS FALSE) as num_of_measurements'))
           // DB::raw('(SELECT ts_date_time FROM bdt_measurement WHERE bdt_measurement.bdt_device_id = client_bdt_device.device_id AND is_cellular_zero IS FALSE ORDER BY ts_date_time DESC LIMIT 1) as most_recent_none_zero_measurement_at'))
        ->join('client', 'client.id', '=', 'client_bdt_device.client_id');

        if($this->performer->pro->pro_type !== 'ADMIN'){
            $devices = $devices->where('client.mcp_pro_id', $this->performer->pro->id);
        }

        $this->filterMultiQuery($request, $devices, 'client_bdt_device.most_recent_measurement_at', 'date_category', 'date_value_1', 'date_value_2');
        
        // $dateKeyName = $request->get('date_category');
        // $date1 = $request->get('date_value_1');
        // $date2 = $request->get('date_value_2');
        // $dateColumnName = "(SELECT (MAX(ts_date_time))::DATE FROM bdt_measurement WHERE bdt_measurement.bdt_device_id = client_bdt_device.device_id AND is_cellular_zero IS FALSE)";
        // switch($dateKeyName) {
        //     case 'EXACTLY':
        //         if($date1) {
        //             $devices->whereRaw($dateColumnName." = '$date1'::DATE");
        //         }
        //         break;
        //     case 'LESS_THAN':
        //         if($date1) {
        //             $devices->whereRaw($dateColumnName. "< '$date1'::DATE" );
        //         }
        //         break;
        //     case 'GREATER_THAN':
        //         if($date1) {
        //             $devices->whereRaw($dateColumnName. "> '$date1'::DATE");
        //         }
        //         break;
        //     case 'BETWEEN':
        //         if($date1 && $date2) {
        //             $devices
        //                 ->whereRaw($dateColumnName. ">= '$date1'::DATE")
        //                 ->whereRaw($dateColumnName. "<= '$date2'::DATE");
        //         }
        //         break;
        //     case 'NOT_BETWEEN':
        //         if($date2 && $date1) {
        //             $devices
        //                 ->where(function ($q) use ($request, $dateColumnName, $date1, $date2) {
        //                     $q->whereRaw($dateColumnName. "< '$date1'::DATE")
        //                         ->orWhereRaw($dateColumnName. "> '$date2'::DATE");
        //                 });
        //         }
        //         break;
        // }

        $keyName = $request->get('measurement_count_category');
        $value1 = $request->get('measurement_count_value_1');
        $value2 = $request->get('measurement_count_value_2');
        $columnName = "(SELECT COUNT(*) FROM bdt_measurement WHERE bdt_measurement.bdt_device_id = client_bdt_device.device_id AND is_cellular_zero IS FALSE)";
        switch($keyName) {
            case 'EXACTLY':
                if($value1) {
                    $devices->whereRaw($columnName.'='.$value1);
                }
                break;
            case 'LESS_THAN':
                if($value1) {
                    $devices->whereRaw($columnName. '<' .$value1);
                }
                break;
            case 'GREATER_THAN':
                if($value1) {
                    $devices->whereRaw($columnName. '>'. $value1);
                }
                break;
            case 'BETWEEN':
                if($value1 && $value2) {
                    $devices
                        ->whereRaw($columnName. '>=' . $value1)
                        ->whereRaw($columnName. '<=' . $value1);
                }
                break;
            case 'NOT_BETWEEN':
                if($value1 && $value2) {
                    $devices
                        ->where(function ($q) use ($request, $columnName, $value1, $value2) {
                            $q->whereRaw($columnName. '<'. $value1)
                                ->orWhereRaw($columnName. '>'.$value2);
                        });
                }
                break;
        }
        
        $status = $request->get('status');
        if($status){
            if($status === 'ACTIVE') $devices = $devices->where('client_bdt_device.is_active', true);
            if($status === 'DEACTIVATED') $devices = $devices->where('client_bdt_device.is_active', false);
        }

        $devices = $devices->orderBy('num_of_measurements', 'DESC')->paginate(20);
        return view('app.practice-management.clients_bdt_devices', compact('devices','filters'));
    }

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

        $memos = ClientMemo::select('client_memo.*')
        ->join('client', 'client.id', '=', 'client_memo.client_id');

        if($this->performer->pro->pro_type !== 'ADMIN'){
            $memos = $memos->where('client.mcp_pro_id', $this->performer->pro->id);
        }

        $this->filterMultiQuery($request, $memos, 'client_memo.created_at', 'date_category', 'date_value_1', 'date_value_2');
        $this->filterSimpleQuery($request, $memos, 'category', 'category');
        $memos = $memos->orderBy('created_at', 'DESC')->paginate(20);

        return view('app.practice-management.memos', compact('memos', 'filters'));
    }

    public function segmentTemplates(Request $request){
        $segmentTemplates = SegmentTemplate::query();
        $segmentTemplates = $segmentTemplates->orderBy('created_at', 'DESC');
        
        $responseType = $request->get('response_type');
        if($responseType && $responseType == 'json'){
            $segmentTemplates = $segmentTemplates->where('is_active', true)->get();
            return $this->pass($segmentTemplates);
        }

        $segmentTemplates = $segmentTemplates->paginate(30);
        return view('app.practice-management.segment-templates.index', compact('segmentTemplates'));
    }

    public function visitTemplates(Request $request){
        $visitTemplates = VisitTemplate::query();
        $visitTemplates = $visitTemplates->orderBy('created_at', 'DESC');

        $responseType = $request->get('response_type');
        if($responseType && $responseType == 'json'){
            $visitTemplates = $visitTemplates->where('is_active', true)->get();
            return $this->pass($visitTemplates);
        }

        $visitTemplates = $visitTemplates->paginate(30);
        return view('app.practice-management.visit-templates.index', compact('visitTemplates'));
    }

    public function visitTemplate(Request $request, VisitTemplate $visitTemplate){
        $visitTemplateSegmentTemplates = VisitTemplateSegmentTemplate::query();
        $visitTemplateSegmentTemplates = $visitTemplateSegmentTemplates->where('visit_template_id', $visitTemplate->id);
        $visitTemplateSegmentTemplates = $visitTemplateSegmentTemplates->orderBy('position_index', 'DESC');
        $visitTemplateSegmentTemplates = $visitTemplateSegmentTemplates->paginate(30);
        return view('app.practice-management.visit-templates.visit-template-segment-templates.index', compact('visitTemplate','visitTemplateSegmentTemplates'));
    }

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

        $pro = $this->performer()->pro;
        if($pro->pro_type !== 'ADMIN') {
            $patients = $patients->where('mcp_pro_id', $this->performer()->pro->id);
        }

        // filters
        /*
        array:18 [▼
          "age_category" => "LESS_THAN"
          "age_value_1" => "34"
          "age_value_2" => null
          "sex" => "M"
          "bmi_category" => "BETWEEN"
          "bmi_value_1" => "20"
          "bmi_value_2" => "25"
          "last_visit_category" => "LESS_THAN"
          "last_visit_value_1" => "2021-10-14"
          "last_visit_value_2" => null
          "next_appointment_category" => "LESS_THAN"
          "next_appointment_value_1" => "2021-10-15"
          "status" => "ACTIVE"
          "last_weighed_in_category" => "EXACTLY"
          "last_weighed_in_value_1" => "2021-10-07"
          "last_bp_category" => "BETWEEN"
          "last_bp_value_1" => "2021-10-01"
          "last_bp_value_2" => "2021-10-31"
        ]
        */

        $this->filterMultiQuery($request, $patients, 'age_in_years', 'age_category', 'age_value_1', 'age_value_2');
        $this->filterSimpleQuery($request, $patients, 'sex', 'sex');
        $this->filterMultiQuery($request, $patients, 'usual_bmi', 'bmi_category', 'bmi_value_1', 'bmi_value_2');
        $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');

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


        if($request->input('is_eligible_for_cm')){
            $patients->where('is_eligible_for_cm', '=', $request->input('is_eligible_for_cm'));
        }

        if($request->input('is_enrolled_in_cm')){
            $patients->where('is_enrolled_in_cm', '=', $request->input('is_enrolled_in_cm'));
        }

        if($request->input('has_cm_setup_been_performed')){
            $patients->where('has_cm_setup_been_performed', '=', $request->input('has_cm_setup_been_performed')=='YES'? true : false);
        }

        if($request->input('is_eligible_for_rm')){
            $patients->where('is_eligible_for_rm', '=', $request->input('is_eligible_for_rm'));
        }

        if($request->input('is_enrolled_in_rm')){
            $patients->where('is_enrolled_in_rm', '=', $request->input('is_enrolled_in_rm'));
        }

        if($request->input('has_rm_setup_been_performed')){
            $patients->where('has_rm_setup_been_performed', '=', $request->input('has_rm_setup_been_performed') =='YES'? true : false);
        }

        $patients = $patients->orderBy('created_at', 'DESC')->paginate(20);
        return view('app.admin.client-ccm-rm-status', compact('patients', 'filters'));
    }

    public function rmActionReport(Request $request){

        $filters = $request->all();
    
        $careMonthStartDate = $request->get('care_month_start_date');
        
        if(!$careMonthStartDate){
            $careMonthStartDate = '2022-01-01';
        }

        $lastDateOfMonth = new DateTime(date("Y-m-t", strtotime(get_current_date())));
        $today = new DateTime(get_current_date());
        $diff = $lastDateOfMonth->diff($today);
        $daysBetweenNowAndEndmonth = $diff->days;
        $minRequiredMeasurements = 16 - $daysBetweenNowAndEndmonth -1;

        $numOfMeasurements = $request->get('num_of_measurements'); //16_or_more, 12_or_more
        $hasRecentVisit = $request->get('has_recent_visit'); //yes no
        $hasBeenSpokenToThisMonth = $request->get('has_been_spoken_to'); //yes no 

        $cmQuery = CareMonth::where('start_date', $careMonthStartDate);

        //remove dummies
        $cmQuery = $cmQuery->whereHas('client', function($clientQuery) {
            return $clientQuery->where('client_engagement_status_category','<>', 'DUMMY')
            ->orWhere('client_engagement_status_category', '=', null);
        });

        if($numOfMeasurements){
            if($numOfMeasurements  == '16_or_more'){
                $cmQuery = $cmQuery->where('number_of_days_with_remote_measurements', '>=', 16);
            }
            if($numOfMeasurements  == 'min_or_more'){
                $cmQuery = $cmQuery->where('number_of_days_with_remote_measurements', '>=', $minRequiredMeasurements)
                ->where('number_of_days_with_remote_measurements', '<', 16);
            }

            if($numOfMeasurements  == 'less_than_min'){
                $cmQuery = $cmQuery->where('number_of_days_with_remote_measurements', '<', $minRequiredMeasurements);
            }
        }

        if($hasRecentVisit){
            if($hasRecentVisit == 'YES'){
                $cmQuery = $cmQuery->whereHas('client', function($clientQuery) {
                    return $clientQuery->whereRaw("most_recent_completed_mcp_note_date::DATE >= ((NOW() - interval '90 days')::DATE)");
                });
            }else{
                $cmQuery = $cmQuery->whereHas('client', function($clientQuery) {
                    return $clientQuery->whereRaw("most_recent_completed_mcp_note_date::DATE < ((NOW() - interval '90 days')::DATE)");
                });
            }
        }

        if($hasBeenSpokenToThisMonth){
            if($hasBeenSpokenToThisMonth  == 'YES'){
                $cmQuery = $cmQuery->where('has_anyone_interacted_with_client_about_rm_outside_note', '=', true);
            }else{
                $cmQuery = $cmQuery->where('has_anyone_interacted_with_client_about_rm_outside_note', '=', false);
            }
           
        }

        $rows = $cmQuery->paginate(50);

        return view('app.practice-management.rm-action-report', compact('rows', 'filters', 'minRequiredMeasurements'));

    }

   
}