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\IncomingReport;
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\Pagination\LengthAwarePaginator;
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;
use App\Models\VisitTemplateAccess;
use App\Models\ClientReviewRequest;
class PracticeManagementController extends Controller
{

    public function incomingReports(Request $request, $filter = '')
    {
        $incomingReports = IncomingReport::where('is_entry_error', '<>', true);
        if($filter === 'not-signed') {
            $incomingReports = $incomingReports->where('has_hcp_pro_signed', '<>', true);
        }
        else if($filter === 'signed') {
            $incomingReports = $incomingReports->where('has_hcp_pro_signed', true);
        }
        $incomingReports = $incomingReports->paginate(25);
        return view('app.practice-management.incoming-reports', compact('incomingReports', 'filter'));
    }

    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', '<=', config('app.maxDaysSinceLastVisit'));
	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)
    {
        $filters = $request->all();
        $rows = ClientProChange::query();
        $associationTypes = DB::table('client_pro_change')->select('responsibility_type')->distinct()->pluck('responsibility_type')->toArray();

        $this->filterMultiQuery($request, $rows, 'created_at', 'date_category', 'date_value_1', 'date_value_2');
        $this->filterSimpleQuery($request, $rows, 'responsibility_type', 'responsibility_type');

        $oldProUid = $request->get('old_pro');
        if($oldProUid){
            $oldPro = Pro::where('uid', $oldProUid)->first();
            $rows = $rows->where('previous_pro_id',$oldPro->id);

        }

        $newProUid = $request->get('new_pro');
        if($newProUid){
            $newPro = Pro::where('uid', $newProUid)->first();
            $rows = $rows->where('new_pro_id',$newPro->id);

        }

        $rows = $rows->orderByRaw('created_at::date DESC')->orderBy('client_id', 'desc')->paginate(20);
        return view ('app.practice-management.client-pro-changes', compact('rows', 'filters', 'associationTypes'));
    }

    public function billingReport(Request $request)
    {

        $filters = $request->all();

        $claimStatus = $request->get('status');

        // conditions
        $conditions = [];
        $conditions[] = "(c.client_engagement_status_category != 'DUMMY')";

        $no_claims = $request->get('no_claims');
        $zero_deductible = $request->get('zero_deductible');
        $claim_status = $request->get('claim_status');
        $verified = $request->get('verified');
        $from = $request->get('from');
        $to = $request->get('to');

        if($no_claims){
            $conditions[] = "(clm.id IS NULL)";
        }

        if($zero_deductible){
            $conditions[] = "(SELECT count(*) FROM client_primary_coverage cpc WHERE
            ((cpc.plan_type = 'MEDICARE' AND cpc.auto_medicare_mpb_deductible > 0) OR (cpc.plan_type = 'COMMERCIAL' AND cpc.deductible_amount > 0))
            AND c.effective_client_primary_coverage_id = cpc.id) = 0";
        }

        if($claim_status){
            $conditions[] = "(clm.status = '{$claim_status}')";
        }

        if($verified){
            if($verified == 'VERIFIED'){
                $conditions[] = "(SELECT count(*) FROM bill b WHERE
            b.is_verified IS TRUE AND b.is_cancelled IS FALSE AND b.note_id = n.id) > 0";
            }

            if($verified == 'UNVERIFIED'){
                $conditions[] = "(SELECT count(*) FROM bill b WHERE
            b.is_verified IS FALSE AND b.is_cancelled IS FALSE AND b.note_id = n.id) > 0";
            }
        }

        if($from) {
            $conditions[] = "(n.effective_dateest::date >= '{$from}')";
        }

        if($to) {
            $conditions[] = "(n.effective_dateest::date <= '{$to}')";
        }

        $from = "FROM note n
         JOIN (SELECT count(*) AS count,
                      bill.note_id
               FROM bill
               WHERE NOT bill.is_cancelled
               GROUP BY bill.note_id) bill_sum ON n.id = bill_sum.note_id
         LEFT JOIN client c ON n.client_id = c.id
         LEFT JOIN pro p ON n.hcp_pro_id = p.id
         LEFT JOIN claim clm on n.id = clm.note_id
         LEFT JOIN client_primary_coverage cpc on c.effective_client_primary_coverage_id = cpc.id";

        $defaultPageSize = 25;

        $page = $request->input('page') ?: 1;
        $perPage = $request->input('per_page') ?: $defaultPageSize;
        $offset = ($page - 1) * $perPage;

        $countQuery = "
SELECT COUNT(*)
$from
WHERE n.is_cancelled IS NOT TRUE AND " . implode(' AND ', $conditions) . "
        ";

        $countResult = DB::select($countQuery);
        $total = $countResult[0]->count;

        $sql = "
SELECT c.id                                                                 AS client_id,
       c.uid                                                                AS client_uid,
       n.id                                                                 AS note_id,
       n.uid                                                                AS note_uid,
       p.id                                                                 AS pro_id,
       p.uid                                                                AS pro_uid,
       (('https://care.leadershiphealth.org/mc/patients/view/'::text || c.uid::text) || '/notes/view/'::text) ||
       n.uid::text                                                          AS link,
       c.name_last                                                          AS client_last,
       c.name_first                                                         AS client_first,
       p.name_last                                                          AS pro_last,
       p.name_first                                                         AS pro_first,
       n.effective_dateest                                                  AS note_date,
       n.new_or_fu                                                          AS new_or_fu_auto,
       n.new_or_fu_or_na,
       n.new_or_fu_or_na::text = n.new_or_fu                                AS new_or_fu_auto_match,
       n.method,
       n.is_bill_closed                                                     AS is_billing_closed,
       n.is_claim_closed                                                    AS is_claiming_closed,
       bill_sum.count                                                       AS bill_count,
       clm.status                                                           AS claim_status,
       cpc.plan_type                                                        AS plan_type,
       cpc.auto_medicare_is_partbprimary                                    AS auto_medicare_is_partbprimary,
       cpc.auto_medicare_mpb_deductible                                     AS medicare_deductible,
       cpc.deductible_amount                                                AS commercial_deductible,
       (SELECT name from payer WHERE payer.id = cpc.commercial_payer_id)    AS payer_name,
       array_to_string(
               ARRAY(SELECT ((((bill.code::text || ' | '::text) || bill.number_of_units::text) || ' units | '::text) ||
                             round(bill.number_of_units * 60::numeric)) || ' mins'::text
                     FROM bill
                     WHERE bill.note_id = n.id), '
'::text)                   AS bills,
       array_to_string(ARRAY(SELECT (note_reason.code || ' | '::text) || note_reason.description
                             FROM note_reason
                             WHERE note_reason.note_id = n.id
                             ORDER BY note_reason.position_index), '
'::text) AS icds,
       array_to_string(ARRAY(SELECT ((cl.cpt::text || ' | '::text) || cl.units::text) || ' units'::text
                             FROM claim_line cl
                             WHERE (cl.claim_id IN (SELECT claim.id
                                                    FROM claim
                                                    WHERE claim.note_id = n.id))
                             ORDER BY cl.position_index), '
'::text)          AS claim_lines,
       array_to_string(ARRAY(SELECT cl2.status FROM claim cl2 WHERE cl2.note_id = n.id
                                                              ORDER BY cl2.created_at DESC), '
'::text)          AS claim_statuses
$from
WHERE n.is_cancelled IS NOT TRUE AND " . implode(' AND ', $conditions) . "
ORDER BY n.effective_dateest DESC NULLS LAST, p.id
OFFSET {$offset} LIMIT {$perPage}
        ";

        $rows = DB::select($sql);

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

        $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
        $perPage = $request->input('per_page') ?: $defaultPageSize;
        $paginator->setPath(route('practice-management.billing-report'));

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

    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 = '')
    {

        $testClientIds = [];
        if(!$request->input('itr')) {
            $testClients = Client::whereRaw("(name_first ILIKE '%test%' OR name_last ILIKE '%test%' OR client_engagement_status_category = 'DUMMY')")->select('id')->get();
            $testClientIds = $testClients->map(function($_x) {return $_x->id;})->toArray();
        }

        $proID = $this->performer()->pro->id;
        $query = Note::where('is_cancelled', FALSE)->where('is_core_note', '<>', true);
        if(!$request->input('itr')) {
            $query = $query->whereNotIn('client_id', $testClientIds);
        }
        if ($this->performer()->pro->pro_type !== 'ADMIN') {
            $query = $query->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')->paginate(30);
        return view('app.practice-management.notes', compact('notes', 'filter'));
    }

    public function allNotes(Request $request)
    {
        $filters = $request->all();
        $query = Note::query();
        $query = $query->where('is_core_note', false);


        $this->filterMultiQuery($request, $query, 'created_at', 'created_at_category', 'created_at_value_1', 'created_at_value_2');
        $this->filterMultiQuery($request, $query, 'effective_dateest', 'effective_date_category', 'effective_date_value_1', 'effective_date_value_2');

        $filterByProsIDs = $request->get('pros');
        if($filterByProsIDs && count($filterByProsIDs)){
            $query = $query->whereIn('hcp_pro_id', $filterByProsIDs);
        }

        $filterByAllyProsIDs = $request->get('ally_pros');
        if($filterByAllyProsIDs && count($filterByAllyProsIDs)){
            $query = $query->whereIn('ally_pro_id', $filterByAllyProsIDs);
        }

        $filterByPatientsIDs = $request->get('patients');
        if($filterByPatientsIDs && count($filterByPatientsIDs)){
            $query = $query->whereIn('client_id', $filterByPatientsIDs);
        }

        $status = $request->get('status');
        if($status){
            if($status == 'CANCELLED') $query = $query->where('is_cancelled', true);
            if($status == 'SIGNED') $query = $query->where('is_signed_by_hcp', true);
            if($status == 'NOT_YET_SIGNED') $query = $query->where('is_signed_by_hcp', false);
            if($status == 'NOT_YET_SIGNED_BUT_ALLY_SIGNED') $query = $query->where('is_signed_by_hcp', false)->where('is_signed_by_ally', true);
            if($status == 'WITH_BILLS') $query = $query->where('is_signed_by_hcp', true)->where('is_cancelled', false)->whereHas('bills');
            if($status == 'WITHOUT_BILLS') $query = $query->where('is_signed_by_hcp', true)->where('is_cancelled', false)->whereDoesntHave('bills');

        }


        $allProsWithNotesIDs = DB::table('note')->pluck('hcp_pro_id')->toArray();
        $allProsWithNotes = Pro::whereIn('id', $allProsWithNotesIDs)->get();

        $allPatientsWithNotesIDs = DB::table('note')->pluck('client_id')->toArray();
        $allPatientsWithNotes = Client::whereIn('id', $allPatientsWithNotesIDs)->get();

        $allAllyProsIDs = DB::table('note')->pluck('ally_pro_id')->toArray();
        $allAllyPros = Pro::whereIn('id', $allAllyProsIDs)->get();

        $notes = $query->orderBy('created_at', 'desc')->paginate(30);

        return view('app.practice-management.all-notes', compact('notes', 'filters','allProsWithNotes', 'allPatientsWithNotes', 'allAllyPros'));
    }

    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;
        $isAdmin = $this->performer()->pro->pro_type === 'ADMIN';
        $query = Bill::where('is_cancelled', false)->whereRaw("(bill_service_type = 'NOTE' OR bill_service_type = 'GENERIC')");
        switch ($filter) {
            case 'not-yet-signed':
                $query = $query
                    ->where(function ($q) use ($proID, $isAdmin) {
                        $q->where(function ($q2) use ($proID, $isAdmin) {
                            $q2->where('is_signed_by_hcp', false);
                            if (!$isAdmin) {
                                $q2->where('hcp_pro_id', $proID);
                            } else {
                                $q2->whereNotNull('hcp_pro_id');
                            }
                        })->orWhere(function ($q2) use ($proID, $isAdmin) {
                            $q2->where('is_signed_by_generic_pro', false);
                            if (!$isAdmin) {
                                $q2->where('generic_pro_id', $proID);
                            }
                            else {
                                $q2->whereNotNull('generic_pro_id');
                            }
                        });
                    });
                break;

            case 'previous':
                $query = $query
                    ->where(function ($q) use ($proID, $isAdmin) {
                        $q->where(function ($q2) use ($proID, $isAdmin) {
                            $q2->where('is_signed_by_hcp', true);
                            if (!$isAdmin) {
                                $q2->where('hcp_pro_id', $proID);
                            } else {
                                $q2->whereNotNull('hcp_pro_id');
                            }
                        })->orWhere(function ($q2) use ($proID, $isAdmin) {
                            $q2->where('is_signed_by_generic_pro', true);
                            if (!$isAdmin) {
                                $q2->where('generic_pro_id', $proID);
                            }
                            else {
                                $q2->whereNotNull('generic_pro_id');
                            }
                        });
                    });
                break;

            // more cases can be added as needed
            default:
                $query = $query
                    ->where(function ($q) use ($proID, $isAdmin) {
                        $q->where(function ($q2) use ($proID, $isAdmin) {
                            if (!$isAdmin) {
                                $q2->where('hcp_pro_id', $proID);
                            } else {
                                $q2->whereNotNull('hcp_pro_id');
                            }
                        })->orWhere(function ($q2) use ($proID, $isAdmin) {
                            if (!$isAdmin) {
                                $q2->where('generic_pro_id', $proID);
                            }
                            else {
                                $q2->whereNotNull('generic_pro_id');
                            }
                        });
                    });
                break;
        }
        $bills = $query->orderBy('created_at', 'desc')->paginate(50);
        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);
        })->where('category', 'other');
        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.effective_client_primary_coverage_id = cpc.id ';
        $withoutCondition = "cl.effective_client_primary_coverage_id IS NULL";
        $pendingCondition = "
(cl.effective_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 processingBillMatrix2(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)->whereRaw("(code = 'RM RMM' OR code = 'RM MCP')");

        $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('effective_date', '>=', $filter);
        }

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

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

        $claimed = $request->input('c');
        if ($claimed && $claimed == 'has-claims') {
            $bills = $bills->whereHas('careMonth', function($cmq){
                return $cmq->whereHas('claims', function($cq){
                    return $cq->where('status', '<>', 'CANCELLED');
                });
            });
        }
        if ($claimed && $claimed == 'no-claims') {
            $bills = $bills->whereHas('careMonth', function($cmq){
                return $cmq->whereDoesntHave('claims', function($cq){
                    return $cq->where('status', '<>', 'CANCELLED');
                });
            });
        }

        // 99454
        $filter = $request->input('c4');
        if ($filter) {
            $q = "(SELECT COUNT(claim_line.id)
FROM claim_line
WHERE claim_line.cpt = '99454' AND
    claim_line.claim_id IN (SELECT claim.id FROM claim WHERE status <> 'CANCELLED' AND care_month_id = bill.care_month_id))";
            if ($filter == 'yes') {
                $bills = $bills->whereRaw("$q > 0");
            }
            else {
                $bills = $bills->whereRaw("$q = 0");
            }
        }

        // 99457
        $filter = $request->input('c7');
        if ($filter) {
            $q = "(SELECT COUNT(claim_line.id)
FROM claim_line
WHERE claim_line.cpt = '99457' AND
    claim_line.claim_id IN (SELECT claim.id FROM claim WHERE status <> 'CANCELLED' AND care_month_id = bill.care_month_id))";
            if ($filter == 'yes') {
                $bills = $bills->whereRaw("$q > 0");
            }
            else {
                $bills = $bills->whereRaw("$q = 0");
            }
        }

        // 99458
        $filter = $request->input('c8');
        if ($filter) {
            $q = "(SELECT COUNT(claim_line.id)
FROM claim_line
WHERE claim_line.cpt = '99458' AND
    claim_line.claim_id IN (SELECT claim.id FROM claim WHERE status <> 'CANCELLED' AND care_month_id = bill.care_month_id))";
            if ($filter == 'yes') {
                $bills = $bills->whereRaw("$q > 0");
            }
            else {
                $bills = $bills->whereRaw("$q = 0");
            }
        }

        $filter = $request->input('clientName');
        if ($filter) {
            $bills = $bills->whereHas('client', function($q) use ($filter){
                return $q->where('name_first', 'ilike', $filter)->orWhere('name_last', 'ilike', $filter);
            });
        }

        $bills = $bills->orderBy('id', '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-matrix2', $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;
        }

        // calculate totals (without pagination)
        $fPros = Pro::whereNotNull('balance')->where('balance', '>', 0)->get();
        $grandTotal = 0;
        $companies = [];
        foreach ($fPros as $row) {
            foreach($row->companyPros as $companyPro) {
                if($companyPro->balance) {
                    if(!isset($companies[$companyPro->company->name])) {
                        $companies[$companyPro->company->name] = 0;
                    }
                    $companies[$companyPro->company->name] += $companyPro->balance;
                    $grandTotal += $companyPro->balance;
                }
            }
        }
        arsort($companies);

        $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', 'grandTotal', 'companies'));
    }

    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::query()->orderBy('name_first', 'asc')->get();
            $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,
       care_month.is_client_enrolled_in_rm,
       client.most_recent_completed_mcp_note_date,
       client.most_recent_completed_mcp_note_id,
       mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
       mrmnote.id as most_recent_completed_mcp_note_id_cm,
       mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
       client.cell_number,
       client.is_assigned_cellular_bp_device,
       client.is_assigned_cellular_weight_scale_device,
       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.rm_total_time_in_seconds_by_rmm_pro,
       care_month.number_of_days_with_remote_measurements,
       care_month.has_mcp_interacted_with_client_about_rm,
       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,
       care_month.mcp_pro_id as care_month_mcp_pro_id,
       care_month.rmm_pro_id as care_month_rmm_pro_id,
       client.mcp_pro_id,
       client.default_na_pro_id,
       client.rmm_pro_id,
       client.rme_pro_id,
       client.cell_number,
       care_month.most_recent_cellular_bp_dbp_mm_hg,
       care_month.most_recent_cellular_bp_sbp_mm_hg,
       care_month.most_recent_cellular_bp_measurement_at,
       care_month.most_recent_cellular_weight_value,
       care_month.most_recent_cellular_weight_measurement_at
FROM care_month join client on care_month.client_id = client.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
     left join note mrmnote on mrmnote.id = (
        select max(n.id) from note n
        where
              n.client_id = client.id AND
              n.is_cancelled = FALSE AND
              (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
              n.effective_dateest::date >= care_month.start_date::date AND
              n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
     )
WHERE
      (care_month.mcp_pro_id = {$performer->pro->id} OR care_month.rmm_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 remoteMonitoring_Row(Request $request) {

        $clientUid = $request->input('clientUid');
        $careMonthUid = $request->input('careMonthUid');

        $query = "
SELECT client.name_first, client.name_last,
       (client.name_first || ' ' || client.name_last) as client_name,
       client.uid as client_uid,
       client.dob,
       care_month.is_client_enrolled_in_rm,
       client.most_recent_completed_mcp_note_date,
       client.most_recent_completed_mcp_note_id,
       mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
       mrmnote.id as most_recent_completed_mcp_note_id_cm,
       mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
       client.cell_number,
       client.is_assigned_cellular_bp_device,
       client.is_assigned_cellular_weight_scale_device,
       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.rm_total_time_in_seconds_by_rmm_pro,
       care_month.number_of_days_with_remote_measurements,
       care_month.has_mcp_interacted_with_client_about_rm,
       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,
       care_month.mcp_pro_id as care_month_mcp_pro_id,
       care_month.rmm_pro_id as care_month_rmm_pro_id,
       client.mcp_pro_id,
       client.default_na_pro_id,
       client.rmm_pro_id,
       client.rme_pro_id,
       client.cell_number,
       care_month.most_recent_cellular_bp_dbp_mm_hg,
       care_month.most_recent_cellular_bp_sbp_mm_hg,
       care_month.most_recent_cellular_bp_measurement_at,
       care_month.most_recent_cellular_weight_value,
       care_month.most_recent_cellular_weight_measurement_at
FROM care_month join client on care_month.client_id = client.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
     left join note mrmnote on mrmnote.id = (
        select max(n.id) from note n
        where
              n.client_id = client.id AND
              n.is_cancelled = FALSE AND
              (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
              n.effective_dateest::date >= care_month.start_date::date AND
              n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
     )
WHERE care_month.uid = '{$careMonthUid}' AND client.uid = '{$clientUid}'
";

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

        return view('app.practice-management.remote-monitoring-row', ['iPatient' => $patients[0], 'trIndex' => $request->input('trIndex')]);
    }

    private function remoteMonitoring_RowByProType(Request $request, $mode) {

        $clientUid = $request->input('clientUid');
        $careMonthUid = $request->input('careMonthUid');

        $query = "
SELECT client.name_first, client.name_last,
       (client.name_first || ' ' || client.name_last) as client_name,
       client.uid as client_uid,
       client.dob,
       care_month.is_client_enrolled_in_rm,
       client.most_recent_completed_mcp_note_date,
       client.most_recent_completed_mcp_note_id,
       mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
       mrmnote.id as most_recent_completed_mcp_note_id_cm,
       mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
       client.cell_number,
       client.is_assigned_cellular_bp_device,
       client.is_assigned_cellular_weight_scale_device,
       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.rm_total_time_in_seconds_by_rmm_pro,
       care_month.number_of_days_with_remote_measurements,
       care_month.has_mcp_interacted_with_client_about_rm,
       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,
       care_month.mcp_pro_id as care_month_mcp_pro_id,
       care_month.rmm_pro_id as care_month_rmm_pro_id,
       client.mcp_pro_id,
       client.default_na_pro_id,
       client.rmm_pro_id,
       client.rme_pro_id,
       client.cell_number,
       care_month.most_recent_cellular_bp_dbp_mm_hg,
       care_month.most_recent_cellular_bp_sbp_mm_hg,
       care_month.most_recent_cellular_bp_measurement_at,
       care_month.most_recent_cellular_weight_value,
       care_month.most_recent_cellular_weight_measurement_at
FROM care_month join client on care_month.client_id = client.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
     left join note mrmnote on mrmnote.id = (
        select max(n.id) from note n
        where
              n.client_id = client.id AND
              n.is_cancelled = FALSE AND
              (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
              n.effective_dateest::date >= care_month.start_date::date AND
              n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
     )
WHERE care_month.uid = '{$careMonthUid}' AND client.uid = '{$clientUid}'
";

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

        return view('app.practice-management.remote-monitoring-row-by-pro-type', ['iPatient' => $patients[0], 'trIndex' => $request->input('trIndex'), 'mode' => $mode]);
    }

    private function remoteMonitoring_RowForAdmin(Request $request) {

        $clientUid = $request->input('clientUid');
        $careMonthUid = $request->input('careMonthUid');

        $query = "
SELECT client.name_first, client.name_last,
       (client.name_first || ' ' || client.name_last) as client_name,
       client.uid as client_uid,
       client.dob,
       care_month.is_client_enrolled_in_rm,
       client.most_recent_completed_mcp_note_date,
       client.most_recent_completed_mcp_note_id,
       mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
       mrmnote.id as most_recent_completed_mcp_note_id_cm,
       mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
       client.cell_number,
       client.is_assigned_cellular_bp_device,
       client.is_assigned_cellular_weight_scale_device,
       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.rm_total_time_in_seconds_by_rmm_pro,
       care_month.number_of_days_with_remote_measurements,
       care_month.has_mcp_interacted_with_client_about_rm,
       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,
       care_month.mcp_pro_id as care_month_mcp_pro_id,
       care_month.rmm_pro_id as care_month_rmm_pro_id,
       client.mcp_pro_id,
       client.default_na_pro_id,
       client.rmm_pro_id,
       client.rme_pro_id,
       client.cell_number,
       care_month.most_recent_cellular_bp_dbp_mm_hg,
       care_month.most_recent_cellular_bp_sbp_mm_hg,
       care_month.most_recent_cellular_bp_measurement_at,
       care_month.most_recent_cellular_weight_value,
       care_month.most_recent_cellular_weight_measurement_at
FROM care_month join client on care_month.client_id = client.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
     left join note mrmnote on mrmnote.id = (
        select max(n.id) from note n
        where
              n.client_id = client.id AND
              n.is_cancelled = FALSE AND
              (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
              n.effective_dateest::date >= care_month.start_date::date AND
              n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
     )
WHERE care_month.uid = '{$careMonthUid}' AND client.uid = '{$clientUid}'
";

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

        return view('app.practice-management.remote-monitoring-row-for-admin', ['iPatient' => $patients[0], 'trIndex' => $request->input('trIndex')]);
    }

    private function rpmMatrixByProType(Request $request, $mode) {

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

        // special case of if-bill-exists
        if($sortBy === 'mcp_rm_generic_bill_id' || $sortBy === 'rmm_rm_generic_bill_id') {
            if($sortDir === 'ASC') {
                $orderBy = "ORDER BY $sortBy $sortDir NULLS FIRST";
            }
            else {
                $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST";
            }
        }

        // default sort
        if(!$request->input('sort_by')) {
            if($mode === 'mcp') {
                $orderBy = "ORDER BY care_month.number_of_days_with_remote_measurements DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_mcp DESC NULLS LAST";
            }
            elseif ($mode === 'rmm') {
                $orderBy = "ORDER BY care_month.number_of_days_with_remote_measurements DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_rmm_pro DESC NULLS LAST";
            }
            else {
                $orderBy = "ORDER BY care_month.number_of_days_with_remote_measurements DESC NULLS LAST";
            }
        }
        else {
            $sortBy = $request->input('sort_by');
            $sortDir = $request->input('sort_dir');
            if($sortBy && $sortDir) {
                if(!in_array($sortBy, ['number_of_days_with_remote_measurements', 'rm_total_time_in_seconds_by_mcp', 'rm_total_time_in_seconds_by_rmm_pro'])) {
                    if($mode === 'mcp') {
                        $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST, care_month.number_of_days_with_remote_measurements DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_mcp DESC NULLS LAST";
                    }
                    elseif ($mode === 'rmm') {
                        $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST, care_month.number_of_days_with_remote_measurements DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_rmm_pro DESC NULLS LAST";
                    }
                    else {
                        $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST, care_month.number_of_days_with_remote_measurements DESC NULLS LAST";
                    }
                }
                else {
                    $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST";
                }
            }
        }

        // filters from the UI
        if(trim($request->input('f_name'))) {
            $v = trim($request->input('f_name'));
            $conditions[] = "(client.name_first ILIKE '%{$v}%' OR client.name_last ILIKE '%{$v}%')";
        }
        if(trim($request->input('f_dob_op')) && trim($request->input('f_dob'))) {
            $o = trim($request->input('f_dob_op'));
            $v = trim($request->input('f_dob'));
            $conditions[] = "(client.dob {$o} '{$v}')";
        }
        if(trim($request->input('f_rpm'))) {
            $v = trim($request->input('f_rpm'));
            if($v === 'yes') {
                $conditions[] = "(care_month.is_client_enrolled_in_rm IS TRUE)";
            }
            elseif($v === 'no') {
                $conditions[] = "(care_month.is_client_enrolled_in_rm IS NOT TRUE)";
            }
        }
        if(trim($request->input('f_cell_bp'))) {
            $v = trim($request->input('f_cell_bp'));
            if($v === 'yes') {
                $conditions[] = "(client.is_assigned_cellular_bp_device IS NOT NULL AND client.is_assigned_cellular_bp_device = TRUE)";
            }
            elseif($v === 'no') {
                $conditions[] = "(client.is_assigned_cellular_bp_device IS NULL OR client.is_assigned_cellular_bp_device = FALSE)";
            }
        }
        if(trim($request->input('f_cell_wt'))) {
            $v = trim($request->input('f_cell_wt'));
            if($v === 'yes') {
                $conditions[] = "(client.is_assigned_cellular_weight_scale_device IS NOT NULL AND client.is_assigned_cellular_weight_scale_device = TRUE)";
            }
            elseif($v === 'no') {
                $conditions[] = "(client.is_assigned_cellular_weight_scale_device IS NULL OR client.is_assigned_cellular_weight_scale_device = FALSE)";
            }
        }
        if(trim($request->input('f_comm'))) {
            $v = trim($request->input('f_comm'));
            if($v === 'yes') {
                $conditions[] = "(care_month.has_mcp_interacted_with_client_about_rm IS NOT NULL AND care_month.has_mcp_interacted_with_client_about_rm = TRUE)";
            }
            elseif($v === 'no') {
                $conditions[] = "(care_month.has_mcp_interacted_with_client_about_rm IS NULL OR care_month.has_mcp_interacted_with_client_about_rm = FALSE)";
            }
        }

        if($mode === 'mcp' || $mode === 'rmm') {
            if(trim($request->input('f_billable'))) {
                $v = trim($request->input('f_billable'));
                if($v === 'yes') {
                    $conditions[] = "(care_month.{$mode}_rm_generic_bill_id IS NOT NULL)";
                }
                elseif($v === 'no') {
                    $conditions[] = "(care_month.{$mode}_rm_generic_bill_id IS NULL)";
                }
            }
        }

        if(trim($request->input('f_md_op')) && trim($request->input('f_md')) !== '') {
            $o = trim($request->input('f_md_op'));
            $v = trim($request->input('f_md'));
            $conditions[] = "(care_month.number_of_days_with_remote_measurements {$o} {$v})";
        }
        if(trim($request->input('f_unst_op')) && trim($request->input('f_unst')) !== '') {
            $o = trim($request->input('f_unst_op'));
            $v = trim($request->input('f_unst'));
            $conditions[] = "(care_month.rm_num_measurements_not_stamped_by_mcp {$o} {$v})";
        }
        if(trim($request->input('f_mins_op')) && trim($request->input('f_mins')) !== '') {
            $o = trim($request->input('f_mins_op'));
            $v = intval(trim($request->input('f_mins'))) * 60;
            $field = 'rm_total_time_in_seconds_by_mcp';
            switch($mode) {
                case 'mcp':
                    $field = 'rm_total_time_in_seconds_by_mcp';
                    break;
                case 'rmm':
                    $field = 'rm_total_time_in_seconds_by_rmm_pro';
                    break;
                break;
            }
            $conditions[] = "(care_month.{$field} {$o} {$v})";
        }

        if($request->input('not-enrolled')) {
            $conditions[] = "(care_month.is_client_enrolled_in_rm IS NOT TRUE)";
        }
        else {
            $conditions[] = "(care_month.is_client_enrolled_in_rm IS TRUE)";
        }

        $proTypeCondition = '';
        $genericBillIdColumns = '';
        $genericBillJoinClause = '';
        switch($mode) {
            case 'mcp':
                $proTypeCondition = "care_month.mcp_pro_id = {$performer->pro->id}";
                $genericBillIdColumns = "care_month.mcp_rm_generic_bill_id,
                   bill.uid as mcp_rm_generic_bill_uid,
                   bill.generic_pro_id as mcp_rm_generic_bill_generic_pro_id,
                   bill.generic_pro_expected_payment_amount as mcp_rm_generic_bill_expected_payment_amount,
                   bill.is_signed_by_generic_pro as mcp_rm_generic_bill_signed";
                $genericBillJoinClause = 'left join bill on care_month.mcp_rm_generic_bill_id = bill.id';
                break;
            case 'rmm':
                $proTypeCondition = "care_month.rmm_pro_id = {$performer->pro->id}";
                $genericBillIdColumns = "care_month.rmm_rm_generic_bill_id,
                   bill.uid as rmm_rm_generic_bill_uid,
                   bill.generic_pro_id as rmm_rm_generic_bill_generic_pro_id,
                   bill.generic_pro_expected_payment_amount as rmm_rm_generic_bill_expected_payment_amount,
                   bill.is_signed_by_generic_pro as rmm_rm_generic_bill_signed";
                $genericBillJoinClause = 'left join bill on care_month.rmm_rm_generic_bill_id = bill.id';
                break;
            case 'rme':
                $proTypeCondition = "care_month.rmm_pro_id = {$performer->pro->id}";
                $genericBillIdColumns = "care_month.rmm_rm_generic_bill_id,
                   bill.uid as rmm_rm_generic_bill_uid,
                   bill.generic_pro_id as rmm_rm_generic_bill_generic_pro_id,
                   bill.generic_pro_expected_payment_amount as rmm_rm_generic_bill_expected_payment_amount,
                   bill.is_signed_by_generic_pro as rmm_rm_generic_bill_signed";
                $genericBillJoinClause = 'left join bill on care_month.rmm_rm_generic_bill_id = bill.id';
                break;
        }

        $query = "
SELECT client.name_first, client.name_last,
       (client.name_first || ' ' || client.name_last) as client_name,
       client.uid as client_uid,
       client.dob,
       care_month.is_client_enrolled_in_rm,
       client.most_recent_completed_mcp_note_date,
       client.most_recent_completed_mcp_note_id,
       mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
       mrmnote.id as most_recent_completed_mcp_note_id_cm,
       mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
       client.cell_number,
       client.is_assigned_cellular_bp_device,
       client.is_assigned_cellular_weight_scale_device,
       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.rm_total_time_in_seconds_by_rmm_pro,
       care_month.number_of_days_with_remote_measurements,
       care_month.has_mcp_interacted_with_client_about_rm,
       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,
       care_month.mcp_pro_id as care_month_mcp_pro_id,
       care_month.rmm_pro_id as care_month_rmm_pro_id,
       care_month.days_between_most_recent_mcp_note_date_and_end_of_care_month,
       client.mcp_pro_id,
       client.default_na_pro_id,
       client.rmm_pro_id,
       client.rme_pro_id,
       client.cell_number,
       care_month.most_recent_cellular_bp_dbp_mm_hg,
       care_month.most_recent_cellular_bp_sbp_mm_hg,
       care_month.most_recent_cellular_bp_measurement_at,
       care_month.most_recent_cellular_weight_value,
       care_month.most_recent_cellular_weight_measurement_at,

	mcp.name_last || ', ' || mcp.name_last AS mcp_name,

       {$genericBillIdColumns}
FROM care_month join client on care_month.client_id = client.id left join pro AS mcp on care_month.mcp_pro_id = mcp.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
    {$genericBillJoinClause}
    left join note mrmnote on mrmnote.id = (
        select max(n.id) from note n
        where
              n.client_id = client.id AND
              n.is_cancelled = FALSE AND
              (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
              n.effective_dateest::date >= care_month.start_date::date AND
              n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
     )
WHERE
      $proTypeCondition
      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
";

        // dd($query);

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

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

        return view('app.practice-management.rpm-matrix-by-pro-type', compact('patients', 'daysRemaining', 'careMonthStart', 'mode'));
    }

    public function rpmMatrixForAdmin(Request $request) {

        if($request->get('ql')) DB::enableQueryLog();

        $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 = []; // ["(care_month.start_date >= '2022-01-01')"];

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

        // special case of if-bill-exists
        if($sortBy === 'mcp_rm_generic_bill_id' || $sortBy === 'rmm_rm_generic_bill_id') {
            if($sortDir === 'ASC') {
                $orderBy = "ORDER BY $sortBy $sortDir NULLS FIRST";
            }
            else {
                $orderBy = "ORDER BY $sortBy $sortDir NULLS LAST";
            }
        }

        // default sort
        if(!$request->input('sort_by')) {
            $orderBy = "ORDER BY care_month.start_date DESC, care_month.number_of_days_with_remote_measurements DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_mcp DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_rmm_pro DESC NULLS LAST";
        }
        else {
            $sortBy = json_decode($request->input('sort_by'));
            $orderByClause = [];
            $includeDefaultKeys = true;
            foreach ($sortBy as $sortCriteria) {
                $orderByClause[] = "{$sortCriteria->key} {$sortCriteria->order} NULLS LAST";
                if(in_array($sortCriteria->key, ['number_of_days_with_remote_measurements', 'rm_total_time_in_seconds_by_mcp', 'rm_total_time_in_seconds_by_rmm_pro'])) {
                    $includeDefaultKeys = false;
                }
            }
            if($includeDefaultKeys) {
                $orderByClause[] = "care_month.number_of_days_with_remote_measurements DESC NULLS LAST";
                $orderByClause[] = "care_month.rm_total_time_in_seconds_by_mcp DESC NULLS LAST";
                $orderByClause[] = "care_month.rm_total_time_in_seconds_by_rmm_pro DESC NULLS LAST";
            }
            $orderBy = 'ORDER BY ' . implode(', ', $orderByClause);
        }

        // filters from the UI
        if(trim($request->input('m'))) {
            $v = trim($request->input('m'));
            $conditions[] = "(EXTRACT(MONTH from care_month.start_date) = $v)";
        }
        if(trim($request->input('y'))) {
            $v = trim($request->input('y'));
            $conditions[] = "(EXTRACT(YEAR from care_month.start_date) = $v)";
        }

        if(trim($request->input('f_ces'))) {
            $v = trim($request->input('f_ces'));
            if($v === 'ACTIVE') {
                $conditions[] = "(client.client_engagement_status_category IS NULL OR client.client_engagement_status_category = '{$v}')";
            }
            else {
                $conditions[] = "(client.client_engagement_status_category = '{$v}')";
            }
        }
        if(trim($request->input('f_name'))) {
            $v = trim($request->input('f_name'));
            $conditions[] = "(client.name_first ILIKE '%{$v}%' OR client.name_last ILIKE '%{$v}%')";
        }

        if(trim($request->input('f_payer_type'))) {
            switch($request->input('f_payer_type')) {
                case 'Medicare':
                    $conditions[] = "(cpc.plan_type = 'MEDICARE')";
                    break;
                case 'Non Medicare':
                    $conditions[] = "(cpc.plan_type != 'MEDICARE')";
                    break;
                case 'Medicaid':
                    $conditions[] = "(cpc.plan_type = 'MEDICAID')";
                    break;
                case 'Commercial':
                    $conditions[] = "(cpc.plan_type = 'COMMERCIAL')";
                    break;
            }
        }

        if((trim($request->input('f_payer_type')) === 'Non Medicare' || trim($request->input('f_payer_type')) === 'Commercial') && trim($request->input('f_payer'))) {
            $v = trim($request->input('f_payer'));
            $conditions[] = "(payer.name ILIKE '%{$v}%')";
        }

        if(trim($request->input('f_state'))) {
            $v = trim($request->input('f_state'));
            switch($v) {
                case 'NONE':
                    $conditions[] = "(client.mailing_address_state IS NULL)";
                    break;
                case 'NOT_MD':
                    $conditions[] = "(client.mailing_address_state IS NOT NULL AND client.mailing_address_state != 'MD')";
                    break;
                default:
                    $conditions[] = "(client.mailing_address_state ILIKE '%{$v}%')";
                    break;
            }
        }

        if(trim($request->input('f_mcp'))) {
            $v = trim($request->input('f_mcp'));
            $conditions[] = "(mcpPro.uid = '{$v}')";
        }
        if(trim($request->input('f_dob_op')) && trim($request->input('f_dob'))) {
            $o = trim($request->input('f_dob_op'));
            $v = trim($request->input('f_dob'));
            $conditions[] = "(client.dob {$o} '{$v}')";
        }
        if(trim($request->input('f_rpm'))) {
            $v = trim($request->input('f_rpm'));
            if($v === 'yes') {
                $conditions[] = "(care_month.is_client_enrolled_in_rm IS TRUE)";
            }
            elseif($v === 'no') {
                $conditions[] = "(care_month.is_client_enrolled_in_rm IS NOT TRUE)";
            }
        }
        if(trim($request->input('f_cell_bp'))) {
            $v = trim($request->input('f_cell_bp'));
            if($v === 'yes') {
                $conditions[] = "(client.is_assigned_cellular_bp_device IS NOT NULL AND client.is_assigned_cellular_bp_device = TRUE)";
            }
            elseif($v === 'no') {
                $conditions[] = "(client.is_assigned_cellular_bp_device IS NULL OR client.is_assigned_cellular_bp_device = FALSE)";
            }
        }
        if(trim($request->input('f_cell_wt'))) {
            $v = trim($request->input('f_cell_wt'));
            if($v === 'yes') {
                $conditions[] = "(client.is_assigned_cellular_weight_scale_device IS NOT NULL AND client.is_assigned_cellular_weight_scale_device = TRUE)";
            }
            elseif($v === 'no') {
                $conditions[] = "(client.is_assigned_cellular_weight_scale_device IS NULL OR client.is_assigned_cellular_weight_scale_device = FALSE)";
            }
        }
        if(trim($request->input('f_lmb'))) {
            $v = trim($request->input('f_lmb'));
            switch($v) {
                case '1':
                    $conditions[] = "(GREATEST(client.most_recent_cellular_bp_measurement_at::DATE, client.most_recent_cellular_weight_measurement_at::DATE)::DATE < (NOW() - INTERVAL '1 DAY')::DATE)";
                    break;
                case '2':
                    $conditions[] = "(GREATEST(client.most_recent_cellular_bp_measurement_at::DATE, client.most_recent_cellular_weight_measurement_at::DATE)::DATE < (NOW() - INTERVAL '2 DAYS')::DATE)";
                    break;
                case '3':
                    $conditions[] = "(GREATEST(client.most_recent_cellular_bp_measurement_at::DATE, client.most_recent_cellular_weight_measurement_at::DATE)::DATE < (NOW() - INTERVAL '3 DAYS')::DATE)";
                    break;
                case '3+':
                    $conditions[] = "(GREATEST(client.most_recent_cellular_bp_measurement_at::DATE, client.most_recent_cellular_weight_measurement_at::DATE)::DATE < (NOW() - INTERVAL '4 DAYS')::DATE)";
                    break;
            }
        }
        if(trim($request->input('f_comm'))) {
            $v = trim($request->input('f_comm'));
            if($v === 'yes') {
                $conditions[] = "(care_month.has_mcp_interacted_with_client_about_rm IS NOT NULL AND care_month.has_mcp_interacted_with_client_about_rm = TRUE)";
            }
            elseif($v === 'no') {
                $conditions[] = "(care_month.has_mcp_interacted_with_client_about_rm IS NULL OR care_month.has_mcp_interacted_with_client_about_rm = FALSE)";
            }
        }

        if(trim($request->input('f_mcp_billable'))) {
            $v = trim($request->input('f_mcp_billable'));
            if($v === 'yes') {
                $conditions[] = "(care_month.mcp_rm_generic_bill_id IS NOT NULL)";
            }
            elseif($v === 'no') {
                $conditions[] = "(care_month.mcp_rm_generic_bill_id IS NULL)";
            }
        }

        if(trim($request->input('f_rmm_billable'))) {
            $v = trim($request->input('f_rmm_billable'));
            if($v === 'yes') {
                $conditions[] = "(care_month.rmm_rm_generic_bill_id IS NOT NULL)";
            }
            elseif($v === 'no') {
                $conditions[] = "(care_month.rmm_rm_generic_bill_id IS NULL)";
            }
        }

        if(trim($request->input('f_md_op')) && trim($request->input('f_md')) !== '') {
            $o = trim($request->input('f_md_op'));
            $v = trim($request->input('f_md'));
            $conditions[] = "(care_month.number_of_days_with_remote_measurements {$o} {$v})";
        }
        if(trim($request->input('f_unst_op')) && trim($request->input('f_unst')) !== '') {
            $o = trim($request->input('f_unst_op'));
            $v = trim($request->input('f_unst'));
            $conditions[] = "(care_month.rm_num_measurements_not_stamped_by_mcp {$o} {$v})";
        }
        if(trim($request->input('f_mcp_mins_op')) && trim($request->input('f_mcp_mins')) !== '') {
            $o = trim($request->input('f_mcp_mins_op'));
            $v = intval(trim($request->input('f_mcp_mins'))) * 60;
            $conditions[] = "(care_month.rm_total_time_in_seconds_by_mcp {$o} {$v})";
        }
        if(trim($request->input('f_rmm_mins_op')) && trim($request->input('f_rmm_mins')) !== '') {
            $o = trim($request->input('f_rmm_mins_op'));
            $v = intval(trim($request->input('f_rmm_mins'))) * 60;
            $conditions[] = "(care_month.rm_total_time_in_seconds_by_rmm_pro {$o} {$v})";
        }

        if(trim($request->input('f_dslm_op')) && trim($request->input('f_dslm')) !== '') {
            $o = trim($request->input('f_dslm_op'));
            $v = trim($request->input('f_dslm'));
            $conditions[] = "(DATE_PART('day', NOW() - client.most_recent_cellular_measurement_at) {$o} {$v})";
        }

        if(trim($request->input('f_dslv_op')) && trim($request->input('f_dslv')) !== '') {
            $o = trim($request->input('f_dslv_op'));
            $v = trim($request->input('f_dslv'));
            $conditions[] = "(care_month.days_between_most_recent_mcp_note_date_and_end_of_care_month {$o} {$v})";
        }

        if($request->input('not-enrolled')) {
            $conditions[] = "(care_month.is_client_enrolled_in_rm IS NOT TRUE)";
        }
        else {
            $conditions[] = "(care_month.is_client_enrolled_in_rm IS TRUE)";
        }

        if($request->input('f_claim_closed')) {
            if($request->input('f_claim_closed') === 'no') {
                $conditions[] = "(care_month.is_claim_closed IS NULL OR care_month.is_claim_closed IS FALSE)";
            }
            else {
                $conditions[] = "(care_month.is_claim_closed IS TRUE)";
            }
        }

        if($request->input('f_mcp_billable')) {
            if($request->input('f_mcp_billable') === 'no') {
                $conditions[] = "(care_month.is_billable_by_mcp IS NULL OR care_month.is_billable_by_mcp IS FALSE)";
            }
            else {
                $conditions[] = "(care_month.is_billable_by_mcp IS TRUE)";
            }
        }

        if($request->input('f_rmm_billable')) {
            if($request->input('f_rmm_billable') === 'no') {
                $conditions[] = "(care_month.is_billable_by_rmm IS NULL OR care_month.is_billable_by_rmm IS FALSE)";
            }
            else {
                $conditions[] = "(care_month.is_billable_by_rmm IS TRUE)";
            }
        }

        switch($request->input('f_454')) {
            case 'Claimable':
                $conditions[] = "(care_month.is_99454_claimable IS TRUE)";
                break;
            case 'Not Claimable':
                $conditions[] = "(care_month.is_99454_claimable IS NULL OR care_month.is_99454_claimable IS FALSE)";
                break;
            case 'Claimed':
                $conditions[] = "(care_month.is_99454_claimable IS TRUE AND care_month.is_99454_claimed IS TRUE)";
                break;
            case 'Not Claimed':
                $conditions[] = "(care_month.is_99454_claimable IS TRUE AND (care_month.is_99454_claimed IS NULL OR care_month.is_99454_claimed IS FALSE))";
                break;
            case 'Waived':
                $conditions[] = "(care_month.is_99454_claimable IS TRUE AND care_month.is_99454_claiming_waived IS TRUE)";
                break;
        }

        switch($request->input('f_454')) {
            case 'Claimable':
                $conditions[] = "(care_month.is_99454_claimable IS TRUE)";
                break;
            case 'Not Claimable':
                $conditions[] = "(care_month.is_99454_claimable IS NULL OR care_month.is_99454_claimable IS FALSE)";
                break;
            case 'Claimed':
                $conditions[] = "(care_month.is_99454_claimable IS TRUE AND care_month.is_99454_claimed IS TRUE)";
                break;
            case 'Not Claimed':
                $conditions[] = "(care_month.is_99454_claimable IS TRUE AND (care_month.is_99454_claimed IS NULL OR care_month.is_99454_claimed IS FALSE))";
                break;
            case 'Waived':
                $conditions[] = "(care_month.is_99454_claimable IS TRUE AND care_month.is_99454_claiming_waived IS TRUE)";
                break;
        }

        switch($request->input('f_457')) {
            case 'Claimable':
                $conditions[] = "(care_month.is_99457_claimable IS TRUE)";
                break;
            case 'Not Claimable':
                $conditions[] = "(care_month.is_99457_claimable IS NULL OR care_month.is_99457_claimable IS FALSE)";
                break;
            case 'Claimed':
                $conditions[] = "(care_month.is_99457_claimable IS TRUE AND care_month.is_99457_claimed IS TRUE)";
                break;
            case 'Not Claimed':
                $conditions[] = "(care_month.is_99457_claimable IS TRUE AND (care_month.is_99457_claimed IS NULL OR care_month.is_99457_claimed IS FALSE))";
                break;
            case 'Waived':
                $conditions[] = "(care_month.is_99457_claimable IS TRUE AND care_month.is_99457_claiming_waived IS TRUE)";
                break;
        }

        switch($request->input('f_458')) {
            case 'Claimable':
                $conditions[] = "(care_month.is_99458_claimable IS TRUE)";
                break;
            case 'Not Claimable':
                $conditions[] = "(care_month.is_99458_claimable IS NULL OR care_month.is_99458_claimable IS FALSE)";
                break;
            case 'Claimed':
                $conditions[] = "(care_month.is_99458_claimable IS TRUE AND care_month.is_99458_claimed IS TRUE)";
                break;
            case 'Not Claimed':
                $conditions[] = "(care_month.is_99458_claimable IS TRUE AND (care_month.is_99458_claimed IS NULL OR care_month.is_99458_claimed IS FALSE))";
                break;
            case 'Waived':
                $conditions[] = "(care_month.is_99458_claimable IS TRUE AND care_month.is_99458_claiming_waived IS TRUE)";
                break;
        }

        if($request->input('f_visit_90_days')) {
            /*if($request->input('f_visit_90_days') === 'yes') {
                $conditions[] = "(care_month.is_claim_closed IS " . ($request->input('f_claim_closed') === 'yes' ? 'TRUE' : 'FALSE') . ")";
            }
            else {
                $conditions[] = "(care_month.is_claim_closed IS " . ($request->input('f_claim_closed') === 'yes' ? 'TRUE' : 'FALSE') . ")";
            }*/
        }

        $genericBillIdColumns = "care_month.mcp_rm_generic_bill_id,
                   mcpBill.uid as mcp_rm_generic_bill_uid,
                   mcpBill.generic_pro_id as mcp_rm_generic_bill_generic_pro_id,
                   mcpBill.generic_pro_expected_payment_amount as mcp_rm_generic_bill_expected_payment_amount,
                   mcpBill.is_signed_by_generic_pro as mcp_rm_generic_bill_signed,
                   care_month.rmm_rm_generic_bill_id,
                   rmmBill.uid as rmm_rm_generic_bill_uid,
                   rmmBill.generic_pro_id as rmm_rm_generic_bill_generic_pro_id,
                   rmmBill.generic_pro_expected_payment_amount as rmm_rm_generic_bill_expected_payment_amount,
                   rmmBill.is_signed_by_generic_pro as rmm_rm_generic_bill_signed";
        $genericBillJoinClause = "left join bill mcpBill on care_month.mcp_rm_generic_bill_id = mcpBill.id
                   left join bill rmmBill on care_month.rmm_rm_generic_bill_id = rmmBill.id";

        $defaultPageSize = 25;
        $page = $request->input('page') ?: 1;
        $perPage = $request->input('per_page') ?: $defaultPageSize;
        $offset = ($page - 1) * $perPage;

        $countQuery = "
SELECT COUNT(*)
FROM care_month
    join client on care_month.client_id = client.id
    left join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
    left join pro mcpPro on care_month.mcp_pro_id = mcpPro.id
    left join pro rmmPro on care_month.rmm_pro_id = rmmPro.id
    left join client_primary_coverage cpc on client.effective_client_primary_coverage_id = cpc.id
    left join payer on cpc.commercial_payer_id = payer.id
    {$genericBillJoinClause}
WHERE
      " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '') . "
";

        $countResult = DB::select($countQuery);
        $total = $countResult[0]->count;

        $query = "
WITH claim_counts AS (
  SELECT
    c.care_month_id,
    COUNT(*) FILTER (WHERE cl.cpt = '99454') AS claim_454,
    COUNT(*) FILTER (WHERE cl.cpt = '99457') AS claim_457,
    COUNT(*) FILTER (WHERE cl.cpt = '99458') AS claim_458
  FROM claim_line cl
  JOIN claim c ON cl.claim_id = c.id
  WHERE c.status <> 'CANCELLED'
  GROUP BY c.care_month_id
)
SELECT client.name_first, client.name_last,
       (client.name_first || ' ' || client.name_last) as client_name,
       client.uid as client_uid,
       client.dob,
       client.age_in_years,
       care_month.is_client_enrolled_in_rm,
       client.most_recent_completed_mcp_note_date,
       client.most_recent_completed_mcp_note_id,
       client.is_assigned_cellular_bp_device,
       client.is_assigned_cellular_weight_scale_device,
       nv.raw_date as next_visit_date,
       DATE_PART('day', NOW() - client.most_recent_cellular_measurement_at) as dslm,
       care_month.uid as care_month_uid,
       care_month.id as care_month_id,
       care_month.is_claim_closed,
       care_month.start_date,
       care_month.rm_total_time_in_seconds,
       care_month.rm_total_time_in_seconds_by_mcp,
       care_month.rm_total_time_in_seconds_by_rmm_pro,
       care_month.number_of_days_with_remote_measurements,
       care_month.has_mcp_interacted_with_client_about_rm,
       care_month.has_anyone_interacted_with_client_about_rm,
       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,
       care_month.is_99454_claimable,
       care_month.is_99454_claimed,
       care_month.is_99454_claiming_waived,
       care_month.is_99457_claimable,
       care_month.is_99457_claimed,
       care_month.is_99457_claiming_waived,
       care_month.is_99458_claimable,
       care_month.is_99458_claimed,
       care_month.is_99458_claiming_waived,
       care_month.is_billable_by_mcp,
       care_month.is_billable_by_rmm,
       care_month.why_99454_not_claimable_reason,
       care_month.why_99457_not_claimable_reason,
       care_month.why_99458_not_claimable_reason,
       care_month.why_claiming_99454_waived,
       care_month.why_claiming_99457_waived,
       care_month.why_claiming_99458_waived,
       care_month.mcp_pro_id as care_month_mcp_pro_id,
       care_month.rmm_pro_id as care_month_rmm_pro_id,
       client.mcp_pro_id,
       client.default_na_pro_id,
       client.rmm_pro_id,
       client.rme_pro_id,
       client.cell_number,
       care_month.most_recent_cellular_bp_dbp_mm_hg,
       care_month.most_recent_cellular_bp_sbp_mm_hg,
       care_month.most_recent_cellular_bp_measurement_at,
       care_month.most_recent_cellular_weight_value,
       care_month.most_recent_cellular_weight_measurement_at,
       mcpPro.mcp_rpm_payment_strategy,
       mcpPro.mcp_rpm_payment_amount,
       (mcpPro.name_first || ' ' || mcpPro.name_last) as mcp_name,
       rmmPro.rmm_payment_strategy,
       rmmPro.rmm_payment_amount,
       client.mailing_address_state,
       care_month.most_recent_cellular_weight_measurement_at,
       care_month.days_between_most_recent_mcp_note_date_and_end_of_care_month,
       cpc.plan_type,
       cpc.auto_medicare_is_partbprimary,
       (CASE
           WHEN cpc.plan_type LIKE 'COMMERCIAL' THEN payer.name
           ELSE cpc.plan_type
       END) as payer_name,
       cc.claim_454,
       cc.claim_457,
       cc.claim_458,
       {$genericBillIdColumns}
FROM care_month join client on care_month.client_id = client.id
    LEFT JOIN claim_counts cc ON cc.care_month_id = care_month.id
    left join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
    left join pro mcpPro on care_month.mcp_pro_id = mcpPro.id
    left join pro rmmPro on care_month.rmm_pro_id = rmmPro.id
    left join client_primary_coverage cpc on client.effective_client_primary_coverage_id = cpc.id
    left join payer on cpc.commercial_payer_id = payer.id
    left join appointment nv on nv.id = mrnote.follow_up_appointment_id
    {$genericBillJoinClause}
WHERE
      " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '') . "
      {$orderBy}  OFFSET {$offset} LIMIT {$perPage}

";

        // dd($query);

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

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

        $paginator = new LengthAwarePaginator($patients, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
        $perPage = $request->input('per_page') ?: $defaultPageSize;
        $paginator->setPath(route('practice-management.rpm-matrix-admin'));

        // counts
        $stats = [];
        $stats['totalPatients'] = $paginator->total();
        $commonStatSQL = "
SELECT COUNT(*)
FROM care_month join client on care_month.client_id = client.id
WHERE
        ";

        $stats['withMeasOn'] = 'N/A';
        $v = $request->input('wmo') ?: date('Y-m-d');
        $statQuery = "$commonStatSQL
EXISTS (SELECT 1 FROM measurement mstat WHERE mstat.care_month_id = care_month.id 
    AND mstat.ts_date_time >= '$v 00:00:00' 
    AND mstat.ts_date_time <= '$v 23:59:59'
    AND mstat.is_cellular_zero IS FALSE) AND
  " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
";
        $statResult = DB::select($statQuery);
        $stats['withMeasOn'] = $statResult[0]->count;

        $stats['noMeasOn'] = 'N/A';
        $v = $request->input('nmo') ?: date('Y-m-d');
        $statQuery = "$commonStatSQL
EXISTS (SELECT 1 FROM measurement mstat WHERE mstat.care_month_id = care_month.id 
    AND mstat.ts_date_time >= '$v 00:00:00' 
    AND mstat.ts_date_time <= '$v 23:59:59'
    AND mstat.is_cellular_zero IS FALSE) AND
  " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
";
        $statResult = DB::select($statQuery);
        $stats['noMeasOn'] = $statResult[0]->count;

        $statQuery = "$commonStatSQL
    care_month.number_of_days_with_remote_measurements >= 16 AND
      " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
";
        $statResult = DB::select($statQuery);
        $stats['gt16MD'] = $statResult[0]->count;

        $statQuery = "$commonStatSQL
    care_month.rm_total_time_in_seconds >= 1200 AND care_month.rm_total_time_in_seconds < 2400 AND
      " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
";
        $statResult = DB::select($statQuery);
        $stats['gt20M'] = $statResult[0]->count;

        $statQuery = "$commonStatSQL
    care_month.rm_total_time_in_seconds >= 2400 AND
      " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
";
        $statResult = DB::select($statQuery);
        $stats['gt40M'] = $statResult[0]->count;

        if($request->get('ql')) dd(DB::getQueryLog());

        return view('app.practice-management.rpm-matrix-for-admin', compact('patients', 'daysRemaining', 'careMonthStart', 'paginator', 'perPage', 'stats'));
    }

    public function rpmManager(Request $request) {

        // whether this pro should be allowed access
        $performer = $this->performer();
        $pro = $performer->pro;
        if($pro->pro_type !== 'ADMIN' &&
            !$pro->is_considered_for_mcp_assignment &&
            !$pro->is_considered_for_rmm &&
            !$pro->is_considered_for_rme) {
            abort(403);
        }

        $proRoles = [];
        if($pro->pro_type === 'ADMIN') $proRoles[] = 'ADMIN';
        if($pro->pro_type === 'ADMIN' || $pro->is_considered_for_mcp_assignment) $proRoles[] = 'MCP';
        if($pro->pro_type === 'ADMIN' || $pro->is_considered_for_rmm) $proRoles[] = 'RMM';
        if($pro->pro_type === 'ADMIN' || $pro->is_considered_for_rme) $proRoles[] = 'RME';
        $viewingAs = $request->input('viewingAs') ?: $proRoles[0];

        $conditions = [];

        if($pro->pro_type !== 'ADMIN') {
            switch ($viewingAs) {
                case 'MCP':
                    $conditions[] = "(care_month.mcp_pro_id = {$pro->id})";
                    break;
                case 'RMM':
                    $conditions[] = "(care_month.rmm_pro_id = {$pro->id})";
                    break;
                case 'RME':
                    $conditions[] = "(care_month.rme_pro_id = {$pro->id})";
                    break;
            }
        }
        else {
            if($request->input('proUid') && $viewingAs !== 'ADMIN') {
                $targetPro = Pro::where('uid', $request->input('proUid'))->first();
                if($targetPro) {
                    switch ($viewingAs) {
                        case 'MCP':
                            $conditions[] = "(care_month.mcp_pro_id = {$targetPro->id})";
                            break;
                        case 'RMM':
                            $conditions[] = "(care_month.rmm_pro_id = {$targetPro->id})";
                            break;
                        case 'RME':
                            $conditions[] = "(care_month.rme_pro_id = {$targetPro->id})";
                            break;
                    }
                }
            }
        }

        if($request->input('mins')) {
            switch ($request->input('mins')) {
                case '<20':
                    $conditions[] = "(care_month.rm_total_time_in_seconds < 1200)";
                    break;
                case '20-40':
                    $conditions[] = "(care_month.rm_total_time_in_seconds >= 1200 AND care_month.rm_total_time_in_seconds < 2400)";
                    break;
                case '40+':
                    $conditions[] = "(care_month.rm_total_time_in_seconds >= 2400)";
                    break;
            }
        }

        // show only if enrolled in RPM
        $conditions[] = "(care_month.is_client_enrolled_in_rm IS TRUE AND client.has_mcp_done_onboarding_visit = 'YES')";

        // m and y are mandatory
        $v = trim($request->input('m'));
        if(!$v) $v = date("m");
        $conditions[] = "(EXTRACT(MONTH from care_month.start_date) = $v)";
        $month = $v;
        $v = trim($request->input('y'));
        if(!$v) $v = date("Y");
        $conditions[] = "(EXTRACT(YEAR from care_month.start_date) = $v)";
        $year = $v;

        $mStr = request()->input('m') ? request()->input('m') : date('m');
        $yStr = request()->input('y') ? request()->input('y') : date('Y');
        $cmStartDate = $yStr . '-' . $mStr . '-' . '01';

        $cmEndDate = date_add(date_create($cmStartDate), date_interval_create_from_date_string("1 month"));
        $cmEndDate = date_sub($cmEndDate, date_interval_create_from_date_string("1 day"));
        $cmEndDate = date_format($cmEndDate, "Y-m-d");

        // count query
        $countQuery = "
SELECT count(*)
FROM care_month join client on care_month.client_id = client.id
    left join pro mcpPro on care_month.mcp_pro_id = mcpPro.id
    left join pro rmmPro on care_month.rmm_pro_id = rmmPro.id
    left join pro rmePro on care_month.rme_pro_id = rmePro.id
    left join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
    left join appointment nv on nv.id = mrnote.follow_up_appointment_id
WHERE
      " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '') . "
";

        $countResult = DB::select($countQuery);
        $total = $countResult[0]->count;

        $orderBy = "care_month.start_date DESC, care_month.number_of_days_with_remote_measurements DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_mcp DESC NULLS LAST, care_month.rm_total_time_in_seconds_by_rmm_pro DESC NULLS LAST";
        if($request->session()->get('rpmManagerPageSize')) {
            $defaultPageSize = intval($request->session()->get('rpmManagerPageSize'));
        }
        else {
            $defaultPageSize = 25;
        }
        $page = $request->input('page') ?: 1;
        $perPage = $defaultPageSize;
        if($request->input('per_page')) { // user input takes precedence
            $perPage = intval($request->input('per_page'));
            $request->session()->put('rpmManagerPageSize', $perPage);
        }

        $offset = ($page - 1) * $perPage;

        // main query
        $query = "
SELECT
       -- display columns
       (client.name_first || ' ' || client.name_last) as client_name,
       client.age_in_years,
       client.dob,
       care_month.start_date,
       mcpPro.name_last as mcp_last_name,
       rmmPro.name_last as rmm_last_name,
       rmePro.name_last as rme_last_name,
       (mcpPro.name_first || ' ' || mcpPro.name_last) as mcp_name,
       (rmmPro.name_first || ' ' || rmmPro.name_last) as rmm_name,
       (rmePro.name_first || ' ' || rmePro.name_last) as rme_name,
       DATE_PART('day', NOW() - client.most_recent_cellular_measurement_at) as dslm,
       care_month.days_between_most_recent_mcp_note_date_and_end_of_care_month,
       care_month.number_of_days_with_remote_measurements,
       care_month.number_of_days_with_remote_bp_measurements,
       care_month.number_of_days_with_remote_weight_measurements,
       care_month.has_cellular_weight_scale_been_ordered,
       care_month.has_cellular_weight_scale_device,
       care_month.most_recent_cellular_weight_value,
       care_month.most_recent_cellular_weight_measurement_at,
       care_month.has_cellular_bp_device,
       care_month.has_cellular_bp_meter_been_ordered,
       care_month.most_recent_cellular_bp_dbp_mm_hg,
       care_month.most_recent_cellular_bp_sbp_mm_hg,
       care_month.most_recent_cellular_bp_measurement_at,
       care_month.entries_json,
       care_month.has_mcp_interacted_with_client_about_rm,
       care_month.has_anyone_interacted_with_client_about_rm,
       care_month.has_admin_interacted_with_client_about_rm,
       care_month.has_rmm_interacted_with_client_about_rm,
       care_month.rm_total_time_in_seconds,
       care_month.rm_total_time_in_seconds_by_mcp,
       care_month.rm_total_time_in_seconds_by_rmm_pro,
       care_month.is_billable_by_mcp,
       care_month.is_billed_by_mcp,
       care_month.is_billable_by_rmm,
       care_month.is_billed_by_rmm,
       care_month.is_billable_by_rme,
       care_month.is_billed_by_rme,
       care_month.is_99454_claimable,
       care_month.is_99454_claimed,
       care_month.is_99454_claiming_waived,
       care_month.why_99454_not_claimable_reason,
       care_month.why_claiming_99454_waived,
       care_month.is_99457_claimable,
       care_month.is_99457_claimed,
       care_month.is_99457_claiming_waived,
       care_month.why_99457_not_claimable_reason,
       care_month.why_claiming_99457_waived,
       care_month.is_99458_claimable,
       care_month.is_99458_claimed,
       care_month.is_99458_claiming_waived,
       care_month.why_99458_not_claimable_reason,
       care_month.why_claiming_99458_waived,
       -- nv.raw_date as next_visit_date,
       (SELECT MIN(appt.raw_date) FROM appointment appt WHERE appt.client_id = client.id AND appt.raw_date > NOW()::DATE) as next_visit_date,
       -- TODO vital settings

       -- functionality support columns
       client.uid as client_uid,
       care_month.uid as care_month_uid,
       mcpPro.mcp_rpm_payment_strategy,
       mcpPro.mcp_rpm_payment_amount,
       rmmPro.rmm_payment_strategy,
       rmmPro.rmm_payment_amount,
       rmePro.rme_payment_strategy,
       rmePro.rme_payment_amount,
       care_month.entries_json,
       care_month.measurements_json,
       care_month.measurements_bp_json,
       care_month.measurements_weight_json,
       care_month.start_date as care_month_start_date,
       care_month.end_date as care_month_end_date,
       mcpBill.has_generic_pro_been_paid as mcp_paid,
       rmmBill.has_generic_pro_been_paid as rmm_paid,
       rmeBill.has_generic_pro_been_paid as rme_paid,
       care_month.rm_reason_icd1,
       care_month.rm_reason_icd2,
       care_month.rm_reason_icd3,
       care_month.rm_reason_icd4
FROM care_month join client on care_month.client_id = client.id
    left join pro mcpPro on care_month.mcp_pro_id = mcpPro.id
    left join pro rmmPro on care_month.rmm_pro_id = rmmPro.id
    left join pro rmePro on care_month.rme_pro_id = rmePro.id
    left join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
    left join appointment nv on nv.id = mrnote.follow_up_appointment_id
    left join bill mcpBill on care_month.mcp_rm_generic_bill_id = mcpBill.id AND mcpBill.is_cancelled IS NOT TRUE
    left join bill rmmBill on care_month.rmm_rm_generic_bill_id = rmmBill.id AND rmmBill.is_cancelled IS NOT TRUE
    left join bill rmeBill on care_month.rme_rm_generic_bill_id = rmeBill.id AND rmeBill.is_cancelled IS NOT TRUE
WHERE
      " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '') . "
      ORDER BY {$orderBy} OFFSET {$offset} LIMIT {$perPage}

";

        $patients = DB::select($query);
        $paginator = new LengthAwarePaginator($patients, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
        $paginator->setPath(route('practice-management.rpm-manager'));

        $mins = $request->input('mins');

        // counts
        $stats = [];
        $stats['totalPatients'] = $paginator->total();
        $commonStatSQL = "
SELECT COUNT(*)
FROM care_month join client on care_month.client_id = client.id
    left join pro mcpPro on care_month.mcp_pro_id = mcpPro.id
    left join pro rmmPro on care_month.rmm_pro_id = rmmPro.id
    left join pro rmePro on care_month.rme_pro_id = rmePro.id
    left join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
    left join appointment nv on nv.id = mrnote.follow_up_appointment_id
    left join bill mcpBill on care_month.mcp_rm_generic_bill_id = mcpBill.id AND mcpBill.is_cancelled IS NOT TRUE
    left join bill rmmBill on care_month.rmm_rm_generic_bill_id = rmmBill.id AND rmmBill.is_cancelled IS NOT TRUE
    left join bill rmeBill on care_month.rme_rm_generic_bill_id = rmeBill.id AND rmeBill.is_cancelled IS NOT TRUE
WHERE
        ";

        $stats['withMeasOn'] = 'N/A';
        $v = $request->input('wmo') ?: date('Y-m-d');
        $statQuery = "$commonStatSQL
EXISTS (SELECT 1 FROM measurement mstat WHERE mstat.care_month_id = care_month.id AND mstat.created_at >= '$v 00:00:00' AND mstat.created_at <= '$v 23:59:59' AND mstat.is_cellular_zero IS FALSE) AND
  " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
";
        $statResult = DB::select($statQuery);
        $stats['withMeasOn'] = $statResult[0]->count;

        $stats['noMeasOn'] = 'N/A';
        $v = $request->input('nmo') ?: date('Y-m-d');
        $statQuery = "$commonStatSQL
NOT EXISTS (SELECT 1 FROM measurement mstat WHERE mstat.care_month_id = care_month.id AND mstat.created_at >= '$v 00:00:00' AND mstat.created_at <= '$v 23:59:59' AND mstat.is_cellular_zero IS FALSE) AND
  " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
";
        $statResult = DB::select($statQuery);
        $stats['noMeasOn'] = $statResult[0]->count;

        $statQuery = "$commonStatSQL
    care_month.number_of_days_with_remote_measurements >= 16 AND
      " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
";
        $statResult = DB::select($statQuery);
        $stats['gt16MD'] = $statResult[0]->count;

        $statQuery = "$commonStatSQL
    care_month.rm_total_time_in_seconds >= 1200 AND care_month.rm_total_time_in_seconds < 2400 AND
      " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
";
        $statResult = DB::select($statQuery);
        $stats['gt20M'] = $statResult[0]->count;

        $statQuery = "$commonStatSQL
    care_month.rm_total_time_in_seconds >= 2400 AND
      " . (count($conditions) > 0 ? implode(" AND ", $conditions) : '1') . "
";
        $statResult = DB::select($statQuery);
        $stats['gt40M'] = $statResult[0]->count;

        return view('app.practice-management.rpm-manager.index', compact('patients', 'month', 'year', 'paginator', 'perPage', 'proRoles', 'viewingAs', 'cmStartDate', 'cmEndDate', 'mins', 'stats'));
    }

    public function rpmManagerRow(Request $request, $uid) {
        $query = "
SELECT
       -- display columns
       (client.name_first || ' ' || client.name_last) as client_name,
       client.age_in_years,
       client.dob,
       care_month.start_date,
       mcpPro.name_last as mcp_last_name,
       rmmPro.name_last as rmm_last_name,
       rmePro.name_last as rme_last_name,
       (mcpPro.name_first || ' ' || mcpPro.name_last) as mcp_name,
       (rmmPro.name_first || ' ' || rmmPro.name_last) as rmm_name,
       (rmePro.name_first || ' ' || rmePro.name_last) as rme_name,
       DATE_PART('day', NOW() - client.most_recent_cellular_measurement_at) as dslm,
       care_month.days_between_most_recent_mcp_note_date_and_end_of_care_month,
       care_month.number_of_days_with_remote_measurements,
       care_month.number_of_days_with_remote_bp_measurements,
       care_month.number_of_days_with_remote_weight_measurements,
       care_month.has_cellular_weight_scale_been_ordered,
       care_month.has_cellular_weight_scale_device,
       care_month.most_recent_cellular_weight_value,
       care_month.most_recent_cellular_weight_measurement_at,
       care_month.has_cellular_bp_device,
       care_month.has_cellular_bp_meter_been_ordered,
       care_month.most_recent_cellular_bp_dbp_mm_hg,
       care_month.most_recent_cellular_bp_sbp_mm_hg,
       care_month.most_recent_cellular_bp_measurement_at,
       care_month.entries_json,
       care_month.has_mcp_interacted_with_client_about_rm,
       care_month.has_anyone_interacted_with_client_about_rm,
       care_month.has_admin_interacted_with_client_about_rm,
       care_month.has_rmm_interacted_with_client_about_rm,
       care_month.rm_total_time_in_seconds,
       care_month.rm_total_time_in_seconds_by_mcp,
       care_month.rm_total_time_in_seconds_by_rmm_pro,
       care_month.is_billable_by_mcp,
       care_month.is_billed_by_mcp,
       care_month.is_billable_by_rmm,
       care_month.is_billed_by_rmm,
       care_month.is_billable_by_rme,
       care_month.is_billed_by_rme,
       care_month.is_99454_claimable,
       care_month.is_99454_claimed,
       care_month.is_99454_claiming_waived,
       care_month.why_99454_not_claimable_reason,
       care_month.why_claiming_99454_waived,
       care_month.is_99457_claimable,
       care_month.is_99457_claimed,
       care_month.is_99457_claiming_waived,
       care_month.why_99457_not_claimable_reason,
       care_month.why_claiming_99457_waived,
       care_month.is_99458_claimable,
       care_month.is_99458_claimed,
       care_month.is_99458_claiming_waived,
       care_month.why_99458_not_claimable_reason,
       care_month.why_claiming_99458_waived,
       -- nv.raw_date as next_visit_date,
       (SELECT MIN(appt.raw_date) FROM appointment appt WHERE appt.client_id = client.id AND appt.raw_date > NOW()::DATE) as next_visit_date,
       -- TODO vital settings

       -- functionality support columns
       client.uid as client_uid,
       care_month.uid as care_month_uid,
       mcpPro.mcp_rpm_payment_strategy,
       mcpPro.mcp_rpm_payment_amount,
       rmmPro.rmm_payment_strategy,
       rmmPro.rmm_payment_amount,
       rmePro.rme_payment_strategy,
       rmePro.rme_payment_amount,
       care_month.entries_json,
       care_month.measurements_json,
       care_month.measurements_bp_json,
       care_month.measurements_weight_json,
       care_month.start_date as care_month_start_date,
       care_month.end_date as care_month_end_date,
       mcpBill.has_generic_pro_been_paid as mcp_paid,
       rmmBill.has_generic_pro_been_paid as rmm_paid,
       rmeBill.has_generic_pro_been_paid as rme_paid,
       care_month.rm_reason_icd1,
       care_month.rm_reason_icd2,
       care_month.rm_reason_icd3,
       care_month.rm_reason_icd4
FROM care_month join client on care_month.client_id = client.id
    left join pro mcpPro on care_month.mcp_pro_id = mcpPro.id
    left join pro rmmPro on care_month.rmm_pro_id = rmmPro.id
    left join pro rmePro on care_month.rme_pro_id = rmePro.id
    left join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
    left join appointment nv on nv.id = mrnote.follow_up_appointment_id
    left join bill mcpBill on care_month.mcp_rm_generic_bill_id = mcpBill.id AND mcpBill.is_cancelled IS NOT TRUE
    left join bill rmmBill on care_month.rmm_rm_generic_bill_id = rmmBill.id AND rmmBill.is_cancelled IS NOT TRUE
    left join bill rmeBill on care_month.rme_rm_generic_bill_id = rmeBill.id AND rmeBill.is_cancelled IS NOT TRUE
WHERE
      care_month.uid = '{$uid}'
";

        $iPatient = DB::select($query)[0];

        $index = $request->input('index');
        $viewingAs = $request->input('viewingAs');
        $daysRemaining = date('t') - date('j');

        // m and y are mandatory
        $v = trim($request->input('m'));
        if(!$v) $v = date("m");
        $month = $v;
        $v = trim($request->input('y'));
        if(!$v) $v = date("Y");
        $year = $v;

        $updateModel = true;

        $specificDate = $request->input('specificDate');

        return view('app.practice-management.rpm-manager.row', compact('iPatient', 'index', 'month', 'year', 'viewingAs', 'updateModel', 'daysRemaining', 'specificDate'));
    }

    public function claimsReport(Request $request) {

        $performer = $this->performer();

        $conditions = ["(claim.status = 'SUBMITTED')"];

        // default sort
        if(!$request->input('sort_by')) {
            $orderBy = "cline.created_at DESC NULLS LAST";
        }
        else {
            $sortBy = json_decode($request->input('sort_by'));
            $orderByClause = [];
            foreach ($sortBy as $sortCriteria) {
                $orderByClause[] = "{$sortCriteria->key} {$sortCriteria->order} NULLS LAST";
            }
            $orderBy = implode(', ', $orderByClause);
        }

        // filters from the UI
        if(trim($request->input('f_start'))) {
            $v = trim($request->input('f_start'));
            $conditions[] = "(cline.date_of_service >= '{$v}')";
        }
        if(trim($request->input('f_end'))) {
            $v = trim($request->input('f_end'));
            $conditions[] = "(cline.date_of_service <= '{$v}')";
        }

        $defaultPageSize = 25;

        $page = $request->input('page') ?: 1;
        $perPage = $request->input('per_page') ?: $defaultPageSize;
        $offset = ($page - 1) * $perPage;

        $countQuery = "
SELECT COUNT(*)
FROM claim_line cline
    join claim on cline.claim_id = claim.id
    join client on claim.client_id = client.id
    left join pro on claim.pro_id = pro.id
    left join client_primary_coverage cpc on client.effective_client_primary_coverage_id = cpc.id
    left join payer on cpc.commercial_payer_id = payer.id
WHERE
      " . implode(' AND ', $conditions) . "
";

        $countResult = DB::select($countQuery);
        $total = $countResult[0]->count;

        $query = "
SELECT (client.name_first || ' ' || client.name_last) as client_name,
       client.uid as client_uid,
       client.dob,
       client.age_in_years,
       client.is_enrolled_in_rm,
       client.mailing_address_state,
       (pro.name_first || ' ' || pro.name_last) as pro_name,
       (CASE
           WHEN cpc.plan_type LIKE 'COMMERCIAL' THEN payer.name
           ELSE cpc.plan_type
       END) as payer_name,
       cline.date_of_service,
       cline.cpt
FROM claim_line cline
    join claim on cline.claim_id = claim.id
    join client on claim.client_id = client.id
    left join pro on claim.pro_id = pro.id
    left join client_primary_coverage cpc on client.effective_client_primary_coverage_id = cpc.id
    left join payer on cpc.commercial_payer_id = payer.id
WHERE
      " . implode(' AND ', $conditions) . "
      ORDER BY {$orderBy} OFFSET {$offset} LIMIT {$perPage}
";

        // dd($query);

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

        $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
        $perPage = $request->input('per_page') ?: $defaultPageSize;
        $paginator->setPath(route('practice-management.claims-report'));

        return view('app.practice-management.claims-report', compact('rows', 'paginator', 'perPage'));
    }

    public function problemsReport(Request $request) {

        $performer = $this->performer();

        $conditions = [];

        // default sort
        if(!$request->input('sort_by')) {
            $orderBy = "p.created_at DESC NULLS LAST";
        }
        else {
            $sortBy = json_decode($request->input('sort_by'));
            $orderByClause = [];
            foreach ($sortBy as $sortCriteria) {
                $orderByClause[] = "{$sortCriteria->key} {$sortCriteria->order} NULLS LAST";
            }
            $orderBy = implode(', ', $orderByClause);
        }

        // filters from the UI

        if(trim($request->input('f_client'))) {
            $v = trim($request->input('f_client'));
            $v = Client::where('uid', $v)->first();
            if($v) {
                $conditions[] = "(c.id = {$v->id})";
            }
        }
        if(trim($request->input('f_mcp'))) {
            $v = trim($request->input('f_mcp'));
            $v = Pro::where('uid', $v)->first();
            if($v) {
                $conditions[] = "(c.mcp_pro_id = {$v->id})";
            }
        }
        if(trim($request->input('f_problem'))) {
            $v = strtolower(trim($request->input('f_problem')));
            if($v) {
                $conditions[] = "(((p.data)::json->>'name')::text ILIKE '%{$v}%')";
            }
        }
        if(trim($request->input('f_icd'))) {
            $v = strtolower(trim($request->input('f_icd')));
            if($v) {
                $conditions[] = "(((p.data)::json->>'icd')::text ILIKE '{$v}%')";
            }
        }
        if(trim($request->input('f_lr_date_op')) && trim($request->input('f_lr_date')) !== '') {
            $o = trim($request->input('f_lr_date_op'));
            $v = trim($request->input('f_lr_date'));
            $conditions[] = "(p.last_child_review_effective_date {$o} '{$v}')";
        }
        if(trim($request->input('f_lr_by'))) {
            $v = trim($request->input('f_lr_by'));
            if($v) {
                $conditions[] = "(p.last_child_review_creator_pro_uid = '{$v}')";
            }
        }
        if(trim($request->input('f_lp_date_op')) && trim($request->input('f_lp_date')) !== '') {
            $o = trim($request->input('f_lp_date_op'));
            $v = trim($request->input('f_lp_date'));
            $conditions[] = "(p.last_child_plan_effective_date {$o} '{$v}')";
        }
        if(trim($request->input('f_lp_by'))) {
            $v = trim($request->input('f_lp_by'));
            if($v) {
                $conditions[] = "(p.last_child_plan_creator_pro_uid = '{$v}')";
            }
        }

        $defaultPageSize = 25;

        $page = $request->input('page') ?: 1;
        $perPage = $request->input('per_page') ?: $defaultPageSize;
        $offset = ($page - 1) * $perPage;

        $countQuery = "
SELECT COUNT(*)
FROM point p
    join client c on p.client_id = c.id
    join pro mcp on c.mcp_pro_id = mcp.id
    left join client_primary_coverage cpc on c.effective_client_primary_coverage_id = cpc.id
    left join payer on cpc.commercial_payer_id = payer.id
WHERE p.category = 'PROBLEM'
    AND p.is_removed_due_to_entry_error IS NOT TRUE " .
    (count($conditions) ? ' AND ' . implode(' AND ', $conditions) : '');

        $countResult = DB::select($countQuery);
        $total = $countResult[0]->count;

        $query = "
SELECT p.uid as point_uid,
       (c.name_first || ' ' || c.name_last) as client_name,
       c.uid as client_uid,
       c.dob,
       c.age_in_years,
       c.is_enrolled_in_rm,
       c.mailing_address_state,
       (mcp.name_first || ' ' || mcp.name_last) as mcp_name,
       (CASE
           WHEN cpc.plan_type LIKE 'COMMERCIAL' THEN payer.name
           ELSE cpc.plan_type
       END) as payer_name,
       row_to_json(p.*) as point,
       ((p.data)::json->>'name')::text as problem,
       ((p.data)::json->>'icd')::text as icd,
       p.last_child_review_effective_date as last_review_date,
       (p.last_child_review_creator_pro_first_name || ' ' || p.last_child_review_creator_pro_last_name) as last_review_by,
       ((last_review.data)::json->>'value')::text as last_review_content,
       p.last_child_plan_effective_date as last_plan_date,
       (p.last_child_plan_creator_pro_first_name || ' ' || p.last_child_plan_creator_pro_last_name) as last_plan_by,
       ((last_plan.data)::json->>'value')::text as last_plan_content
FROM point p
    join client c on p.client_id = c.id
    join pro mcp on c.mcp_pro_id = mcp.id
    left join client_primary_coverage cpc on c.effective_client_primary_coverage_id = cpc.id
    left join payer on cpc.commercial_payer_id = payer.id
    left join point last_review on p.last_child_review_point_id = last_review.id
    left join point last_plan on p.last_child_plan_point_id = last_plan.id
WHERE p.category = 'PROBLEM'
    AND p.is_removed_due_to_entry_error IS NOT TRUE " .
    (count($conditions) ? ' AND ' . implode(' AND ', $conditions) : '') . "
    ORDER BY {$orderBy} OFFSET {$offset} LIMIT {$perPage}";

        // dd($query);

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

        $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
        $perPage = $request->input('per_page') ?: $defaultPageSize;
        $paginator->setPath(route('practice-management.problems-report'));

        return view('app.practice-management.problems-report', compact('rows', 'paginator', 'perPage'));
    }

    public function remoteMonitoringMCP(Request $request) {
        return $this->rpmMatrixByProType($request, 'mcp');
    }

    public function remoteMonitoringRMM(Request $request) {
        return $this->rpmMatrixByProType($request, 'rmm');
    }

    public function remoteMonitoringRME(Request $request) {
        return $this->rpmMatrixByProType($request, 'rme');
    }

    public function remoteMonitoring_RowMCP(Request $request) {
        return $this->remoteMonitoring_RowByProType($request, 'mcp');
    }

    public function remoteMonitoring_RowRMM(Request $request) {
        return $this->remoteMonitoring_RowByProType($request, 'rmm');
    }

    public function remoteMonitoring_RowRME(Request $request) {
        return $this->remoteMonitoring_RowByProType($request, 'rme');
    }

    public function remoteMonitoring_RowADMIN(Request $request) {
        return $this->remoteMonitoring_RowForAdmin($request);
    }

    public function rpm_work_matrix(Request $request) {

        // get the patient having most recent unstamped measurement
        $performer = $this->performer();

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

        $patient = null;
        $careMonth = null;

        if($request->input('patientUid') && $request->input('careMonthUid')) {
            $patient = Client::where('uid', $request->input('patientUid'))->first();
            $careMonth = CareMonth::where('uid', $request->input('careMonthUid'))->first();
        }

        if(!$patient && !$careMonth) {

            $query = "
    SELECT client.name_first, client.name_last,
           (client.name_first || ' ' || client.name_last) as client_name,
           client.uid as client_uid,
           client.dob,
           care_month.is_client_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.rm_num_measurements_not_stamped_by_mcp,
           care_month.rm_num_measurements_not_stamped_by_rmm,
           care_month.days_between_most_recent_mcp_note_date_and_end_of_care_month,
           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,
           GREATEST(client.most_recent_cellular_bp_measurement_at, client.most_recent_cellular_weight_measurement_at) as latest_measurement_at
    FROM care_month join client on care_month.client_id = client.id
    WHERE
          (
              (care_month.mcp_pro_id = {$performer->pro->id} AND care_month.rm_num_measurements_not_stamped_by_mcp > 0) OR
              (care_month.rmm_pro_id = {$performer->pro->id} AND care_month.rm_num_measurements_not_stamped_by_rmm > 0)
          )
          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())') . "
          ORDER BY latest_measurement_at DESC
          LIMIT 1
    ";

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

            if (count($patients)) {
                $patient = Client::where('uid', $patients[0]->client_uid)->first();
                $careMonth = CareMonth::where('uid', $patients[0]->care_month_uid)->first();
            }
        }

        return view('app.practice-management.rpm_work_matrix', compact('patient', 'careMonth'));

    }

    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) {

        $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,
       (mcp_pro.name_first || ' ' || mcp_pro.name_last) as mcp_pro_name,
       (rmm_pro.name_first || ' ' || rmm_pro.name_last) as rmm_pro_name,
       client.uid as client_uid,
       client.chart_number,
       client.dob,
       care_month.is_client_enrolled_in_rm,
       client.most_recent_completed_mcp_note_date,
       client.most_recent_completed_mcp_note_id,
       mrmnote.effective_dateest::date as most_recent_completed_mcp_note_date_cm,
       mrmnote.id as most_recent_completed_mcp_note_id_cm,
       mrmnote.uid as most_recent_completed_mcp_note_uid_cm,
       client.cell_number,
       client.is_assigned_cellular_bp_device,
       client.is_assigned_cellular_weight_scale_device,
       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.rm_total_time_in_seconds_by_rmm_pro,
       care_month.number_of_days_with_remote_measurements,
       care_month.has_anyone_interacted_with_client_about_rm,
       care_month.has_mcp_interacted_with_client_about_rm,
       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,
       care_month.mcp_pro_id as care_month_mcp_pro_id,
       care_month.rmm_pro_id as care_month_rmm_pro_id,
       client.mcp_pro_id,
       client.default_na_pro_id,
       client.rmm_pro_id,
       client.rme_pro_id,
       client.cell_number,
       care_month.most_recent_cellular_bp_dbp_mm_hg,
       care_month.most_recent_cellular_bp_sbp_mm_hg,
       care_month.most_recent_cellular_bp_measurement_at,
       care_month.most_recent_cellular_weight_value,
       care_month.most_recent_cellular_weight_measurement_at
FROM care_month join client on care_month.client_id = client.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
     left join note mrmnote on mrmnote.id = (
        select max(n.id) from note n
        where
              n.client_id = client.id AND
              n.is_cancelled = FALSE AND
              (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
              n.effective_dateest::date >= care_month.start_date::date AND
              n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
     )
    left join pro mcp_pro on care_month.mcp_pro_id = mcp_pro.id
    left join pro rmm_pro on care_month.rmm_pro_id = rmm_pro.id
WHERE
      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-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 care_month.is_client_enrolled_in_rm IS TRUE
      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 = "care_month.is_client_enrolled_in_rm IS TRUE";
        $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) > ".config('app.maxDaysSinceLastVisit');
        $c_lastVisitWithin90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) <= ".config('app.maxDaysSinceLastVisit');
        $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 = "care_month.is_client_enrolled_in_rm IS TRUE";
        $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) > ".config('app.maxDaysSinceLastVisit');
        $c_lastVisitWithin90Days = "DATE_PART('day', care_month.start_date::timestamp - client.most_recent_completed_mcp_note_date::timestamp) <= ".config('app.maxDaysSinceLastVisit');
        $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 client.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,
       care_month.start_date as care_month_start__date,
       -- 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', 'ASC');
        $visitTemplateSegmentTemplates = $visitTemplateSegmentTemplates->paginate(50);
        return view('app.practice-management.visit-templates.visit-template-segment-templates.index', compact('visitTemplate','visitTemplateSegmentTemplates'));
    }

    public function visitTemplateAccess(Request $request, VisitTemplate $visitTemplate){
        $visitTemplateAccesses = VisitTemplateAccess::where('visit_template_id', $visitTemplate->id)->paginate(50);
        return view('app.practice-management.visit-templates.visit-template-accesses.index', compact('visitTemplate','visitTemplateAccesses'));
    }

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

        $pro = $this->performer()->pro;
        if($pro->pro_type !== 'ADMIN') {
            if($pro->is_hcp){
               $patients = $patients->where('mcp_pro_id', $this->performer()->pro->id);
            }
            if($pro->is_considered_for_dna){
                $patients = $patients->where('default_na_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', false);
        $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')){  /*-- correct --*/
            $patients->where('is_enrolled_in_rm', '=', $request->input('is_enrolled_in_rm')); /*-- correct --*/
        }

        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');
        $careMonthYear = $request->get('care_month_year');

        if(!$careMonthStartDate){
            $careMonthStartDate = '2022-01-01';
        }

        if($careMonthYear){
            $careMonthStartDate = $careMonthYear . "-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

        // default sort
        if(!$request->input('sort_by')) {
            $orderBy = "cm.start_date ASC NULLS LAST, cm.client_id ASC NULLS LAST";
        }
        else {
            $sortBy = json_decode($request->input('sort_by'));
            $orderByClause = [];
            $includeDefaultKeys = true;
            foreach ($sortBy as $sortCriteria) {
                $orderByClause[] = "{$sortCriteria->key} {$sortCriteria->order} NULLS LAST";
            }
            $orderBy = implode(', ', $orderByClause);
        }

        $conditions = [];

        // start date
        $conditions[] = "(cm.start_date >= '$careMonthStartDate')";

        if($request->input('rm_eligible')) {
            $conditions[] = "(c.is_eligible_for_rm = '" . $request->input('rm_eligible') . "')";
        }
        if($request->input('rm_enrolled')) {
            if($request->input('rm_enrolled') === 'YES') {
                $conditions[] = "(cm.is_client_enrolled_in_rm IS TRUE)";
            }
            else {
                $conditions[] = "(cm.is_client_enrolled_in_rm IS NOT TRUE)";
            }
        }
        if($request->input('rm_setup')) {
            $conditions[] = "(c.has_rm_setup_been_performed IS " . ($request->input('rm_setup') === 'YES' ? 'TRUE' : 'FALSE') . ")";
        }

        // measurement days
        if($numOfMeasurements){
            if($numOfMeasurements  == '16_or_more') {
                $conditions[] = "(cm.number_of_days_with_remote_measurements >= 16)";
            }
            elseif($numOfMeasurements  == 'min_or_more') {
                $conditions[] = "(cm.number_of_days_with_remote_measurements >= $minRequiredMeasurements AND cm.number_of_days_with_remote_measurements < 16)";
            }
            elseif($numOfMeasurements  == 'less_than_min') {
                $conditions[] = "(cm.number_of_days_with_remote_measurements < $minRequiredMeasurements)";
            }
        }

        // days since last visit
        if($hasRecentVisit){
            if($hasRecentVisit == 'YES'){
                $conditions[] = "(c.most_recent_completed_mcp_note_date >= ((NOW() - interval '".config('app.maxDaysSinceLastVisit')." days')::DATE))";
            }else{
                $conditions[] = "(c.most_recent_completed_mcp_note_date::DATE < ((NOW() - interval '".config('app.maxDaysSinceLastVisit')." days')::DATE))";
            }
        }

        // communicated
        if($hasBeenSpokenToThisMonth){
            if($hasBeenSpokenToThisMonth  == 'YES') {
                $conditions[] = "(cm.has_anyone_interacted_with_client_about_rm_outside_note IS TRUE)";
            }
            else {
                $conditions[] = "(cm.has_anyone_interacted_with_client_about_rm_outside_note IS NOT TRUE)";
            }
        }

        // claiming closed
        $claimingClosed = $request->get('claiming_closed');
        if($claimingClosed){
            if($claimingClosed === 'YES') {
                $conditions[] = "(cm.is_claim_closed IS TRUE)";
            }
            elseif($claimingClosed === 'NO') {
                $conditions[] = "(cm.is_claim_closed IS TRUE)";
            }
        }

        // mcp
        if($request->input('mcp_uid')) {
            $mcp = Pro::where('uid', $request->input('mcp_uid'))->first();
            if($mcp) {
                $conditions[] = "(cm.mcp_pro_id = $mcp->id)";
            }
        }

        // client status
        if($request->input('status')) {
            $v = trim($request->input('status'));
            if($v === 'ACTIVE') {
                $conditions[] = "(c.client_engagement_status_category IS NULL OR c.client_engagement_status_category = '{$v}')";
            }
            else {
                $conditions[] = "(c.client_engagement_status_category = '{$v}')";
            }
        }
        else {
            $conditions[] = "(c.client_engagement_status_category IS NULL OR c.client_engagement_status_category = 'ACTIVE')";
        }

        $columns = "
            cm.id as care_month_id,
            cm.uid as care_month_uid,
            cm.start_date,
            c.uid as client_uid,
            c.is_eligible_for_rm,
            cm.is_client_enrolled_in_rm,
            c.has_rm_setup_been_performed,
            (c.name_first || ' ' || c.name_last) as client_name,
            (mcp.name_first || ' ' || mcp.name_last) as mcp_name,
            (rmm.name_first || ' ' || rmm.name_last) as rmm_name,
            cm.number_of_days_with_remote_measurements,
            DATE_PART('day', NOW() - c.most_recent_cellular_measurement_at) as dslm,
            c.most_recent_completed_mcp_note_date as mr_note_date,
            cm.days_between_most_recent_mcp_note_date_and_end_of_care_month,
            mrnote.uid as mr_note_uid,
            cm.rm_total_time_in_seconds,
            cm.rm_total_time_in_seconds_by_mcp,
            cm.rm_total_time_in_seconds_by_rmm_pro,
            cm.has_admin_interacted_with_client_about_rm,
            cm.has_mcp_interacted_with_client_about_rm,
            cm.claim_suggestion_json,
            cm.is_claim_closed,
            (CASE
               WHEN cm.mcp_rm_generic_bill_id IS NOT NULL AND mcp_rm_bill.is_cancelled IS NOT TRUE THEN TRUE
               ELSE FALSE
            END) as mcp_payable,
            (CASE
               WHEN cm.rmm_rm_generic_bill_id IS NOT NULL AND rmm_rm_bill.is_cancelled IS NOT TRUE THEN TRUE
               ELSE FALSE
            END) as rmm_payable,
            mcp_rm_bill.uid as mcp_rm_bill_uid,
            rmm_rm_bill.uid as rmm_rm_bill_uid,
            mcp_rm_bill.code as mcp_rm_bill_code,
            rmm_rm_bill.code as rmm_rm_bill_code,
            cl.status as claim_status,
            cl.uid as claim_uid,
            array_to_string(
                ARRAY(SELECT claim_line.cpt FROM claim_line WHERE claim_line.claim_id = cl.id),
                ', '::text
                ) AS cpts
        ";

        $from = "
            care_month cm
            join client c on cm.client_id = c.id
            left join pro mcp on c.mcp_pro_id = mcp.id
            left join pro rmm on c.rmm_pro_id = rmm.id
            left join bill mcp_rm_bill on cm.mcp_rm_generic_bill_id = mcp_rm_bill.id
            left join bill rmm_rm_bill on cm.rmm_rm_generic_bill_id = rmm_rm_bill.id
            left join note mrnote on c.most_recent_completed_mcp_note_id = mrnote.id
            left join claim cl on cl.care_month_id = cm.id AND cl.is_cancelled IS NOT TRUE
        ";

        $page = $request->input('page') ?: 1;
        $perPage = $request->input('per_page') ?: 15;
        $offset = ($page - 1) * $perPage;

        $countSql = "
        SELECT
            COUNT(*)
        FROM
            $from
        where " . implode(" AND ", $conditions) . "
            AND cl.is_cancelled IS NOT TRUE
        ";

        $countResult = DB::select($countSql);
        $total = $countResult[0]->count;

        $sql = "
        SELECT
            $columns
        FROM
            $from
        WHERE " . implode(" AND ", $conditions) . "
        ORDER BY $orderBy
        OFFSET {$offset} LIMIT {$perPage}
        ";

        $rows = DB::select($sql);

        $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: 15, $request->input('page') ?: 1);
        $perPage = $request->input('per_page') ?: 15;
        $paginator->setPath(route('practice-management.rmActionReport'));

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

    }

    public function myFlyers(Request $request){
        $pro = $this->performer->pro;
        $slug = $pro->slug ?? $pro->uid;
        $url = config('app.stagfe6_url') . '/flyers/json-list?slug=' . $slug;

        $arrContextOptions=array(
            "ssl"=>array(
                "verify_peer"=>false,
                "verify_peer_name"=>false,
            ),
        );
        $response = @file_get_contents($url, false, stream_context_create($arrContextOptions));
        $response = @json_decode($response);
        $flyerTemplates = [];
        if(isset($response->data)){
            $flyerTemplates = $response->data;
        }
        return view('app.my-flyers', compact('pro','flyerTemplates'));

    }

    public function notesPendingPhysicianSupervisorStamping(Request $request){
        $hideTitle = $request->get('hideTitle');
        $pro = $this->performer->pro;
        //All companyProIds that I am supervisor
        $flaggedNotes = Note::where('is_stamped_by_supervising_physician', false)->where('is_flagged_for_supervising_physician_review', true)->whereHas('hcpCompanyPro', function($qry) use($pro){
            return $qry->whereHas('supervisingPhysicianCompanyPro', function($qr) use($pro){
                return $qr->where('pro_id', $pro->id);
            });
        })->get();
        return view('app.notes-pending-physician-supervisor-stamping', compact('flaggedNotes', 'hideTitle'));
    }

    public function clientsWithoutDefaultCompanyProPayer(Request $request){
        $rows = \App\Models\Client::whereNull('shadow_pro_id')
            ->where('is_active', true)
            ->where(function ($q) {
                $q->whereNull('client_engagement_status_category')
                    ->orWhere('client_engagement_status_category', 'ACTIVE');
            })
            ->whereRaw('(default_mcp_company_pro_payer_id IS NULL OR default_mcp_company_location_id IS NULL)')
            ->orderBy('created_at', 'DESC')
            ->paginate(25);
        return view('app.clients-without-default-company-pro-payer', compact('rows'));
    }

    public function notesWithoutHcpCompanyProPayer(Request $request){
        $rows = \App\Models\Note::where('is_cancelled', false)
            ->where('is_core_note', false)
            ->where('is_signed_by_hcp', true)
            ->whereNull('hcp_company_pro_payer_id')
            ->orderBy('created_at', 'DESC')
            ->paginate(25);
        return view('app.notes-without-hcp-company-pro-payer', compact('rows'));
    }

    public function noteHcpBillsWithoutCompanyPro(Request $request){
        $rows = \App\Models\Bill::where('bill_service_type', 'NOTE')
            ->where('is_cancelled', false)
            ->whereNotNull('hcp_pro_id')
            ->whereNull('hcp_company_pro_id')
            ->where('has_hcp_been_paid', false)
            ->orderBy('created_at', 'DESC')
            ->paginate(25);
        return view('app.note-hcp-bills-without-company-pro', compact('rows'));
    }

    public function rpmMcpBillsWithoutCompanyPro(Request $request){
        $rows = \App\Models\Bill::where('bill_service_type', 'GENERIC')
            ->where('is_cancelled', false)
            ->whereNotNull('generic_pro_id')
            ->whereNull('generic_company_pro_id')
            ->where('has_generic_pro_been_paid', false)
            ->where('code', 'RM MCP')
            ->orderBy('created_at', 'DESC')
            ->paginate(25);
        return view('app.rpm-mcp-bills-without-company-pro', compact('rows'));
    }

    public function rpmRmmBillsWithoutCompanyPro(Request $request){
        $rows = \App\Models\Bill::where('bill_service_type', 'GENERIC')
            ->where('is_cancelled', false)
            ->whereNotNull('generic_pro_id')
            ->whereNull('generic_company_pro_id')
            ->where('has_generic_pro_been_paid', false)
            ->where('code', 'RM RMM')
            ->orderBy('created_at', 'DESC')
            ->paginate(25);
        return view('app.rpm-rmm-bills-without-company-pro', compact('rows'));
    }

    public function notesResolutionCenter(Request $request) {

        $columns = "(c.name_first || ' ' || c.name_last) as client_name,
            (hcp.name_first || ' ' || hcp.name_last) as hcp_name,
            (na.name_first || ' ' || na.name_last) as na_name,
            c.chart_number,
            c.uid as client_uid,
            n.effective_dateest,
            n.uid,
            n.detail_json,
	        n.is_claim_closed,
	        n.visit_number,
            ROUND(b.number_of_units * 60) as minutes,

 n.note_reason_icd1 AS icd1,
 n.note_reason_icd1description AS icd1description,
 n.note_reason_icd2 AS icd2,
 n.note_reason_icd2description AS icd2description,
 n.note_reason_icd3 AS icd3,
 n.note_reason_icd3description AS icd3description,
 n.note_reason_icd4 AS icd4,
 n.note_reason_icd4description AS icd4description,
 n.note_reason_memo AS icd_memo,
 n.visit_number

            ";
        $from = "FROM note AS n
            LEFT JOIN pro AS hcp ON n.hcp_pro_id = hcp.id
            LEFT JOIN pro AS na ON n.ally_pro_id = na.id
            JOIN client AS c ON n.client_id = c.id
	    JOIN bill AS b ON (b.note_id = n.id AND b.is_cancelled IS NOT TRUE AND b.code ILIKE '%treatment%')
            ";
        $where = "WHERE
            -- n.visit_number IN (1,2) AND
	    n.is_signed_by_hcp IS TRUE AND
            -- n.is_claim_closed IS NOT TRUE AND
            n.is_cancelled IS NOT TRUE AND
            n.created_at::DATE >= '2022-01-01'::DATE AND
            c.client_engagement_status_category <> 'DUMMY' AND
            c.name_first NOT ILIKE '%test%' AND
            c.name_last NOT ILIKE '%test%' AND
            n.id IN (SELECT note_id FROM bill WHERE code ILIKE '%treatment%' AND bill.is_cancelled IS NOT TRUE AND note_id IS NOT NULL) AND
            -- n.id NOT IN (SELECT note_id FROM claim WHERE note_id IS NOT NULL) AND
            c.is_part_b_primary = 'YES' AND
            c.latest_eligible_refresh_at::DATE >= '2022-01-01' AND
            c.mpb_remaining = 0 AND
            c.created_at::DATE >= '2022-01-01'::DATE
            ";
        $filters = [];
        if($request->input('f')) {
            $filters[] =  "(n.detail_json IS NOT NULL AND ((n.detail_json)::json->'farah_decision')::text = '\"" . $request->input('f') . "\"')";
        }
        if($request->input('s')) {
            $filters[] =  "(n.detail_json IS NOT NULL AND ((n.detail_json)::json->'shawn_decision')::text = '\"" . $request->input('s') . "\"')";
        }
        if(count($filters)) {
            $filters = 'AND ' . implode(' AND ',  $filters);
        }
        else {
            $filters = '';
        }
        // $filters = '';
        $orderBy = "ORDER BY c.id ASC, n.visit_number ASC";

        $countQuery = "SELECT count(*) {$from} {$where} {$filters}";
        // dd($countQuery);
        $countResult = DB::select($countQuery);
        $total = $countResult[0]->count;

	$defaultPageSize = 25;

        $page = $request->input('page') ?: 1;
        $perPage = $request->input('per_page') ?: $defaultPageSize;
        $offset = ($page - 1) * $perPage;

        $dataQuery = "SELECT {$columns} {$from} {$where} {$filters} {$orderBy} OFFSET {$offset} LIMIT {$perPage}";
        $rows = DB::select($dataQuery);

        $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
        $paginator->setPath(route('practice-management.notes-resolution-center'));

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

    public function notesResolutionCenterV2(Request $request) {

        $columns = "(c.name_first || ' ' || c.name_last) as client_name,
            (hcp.name_first || ' ' || hcp.name_last) as hcp_name,
            (na.name_first || ' ' || na.name_last) as na_name,
            c.chart_number,
            c.uid as client_uid,
            n.effective_dateest,
            n.uid,
            n.detail_json,
	        n.is_claim_closed,
	        n.visit_number,
            ROUND(b.number_of_units * 60) as minutes,

 		n.note_reason_icd1 AS icd1,
 		n.note_reason_icd1description AS icd1description,
 		n.note_reason_icd2 AS icd2,
 		n.note_reason_icd2description AS icd2description,
 		n.note_reason_icd3 AS icd3,
 		n.note_reason_icd3description AS icd3description,
 		n.note_reason_icd4 AS icd4,
 		n.note_reason_icd4description AS icd4description,
 		n.note_reason_memo AS icd_memo

            ";
        $from = "FROM note AS n
            LEFT JOIN pro AS hcp ON n.hcp_pro_id = hcp.id
            LEFT JOIN pro AS na ON n.ally_pro_id = na.id
            JOIN client AS c ON n.client_id = c.id
	    JOIN bill AS b ON (b.note_id = n.id AND b.is_cancelled IS NOT TRUE AND b.code ILIKE '%treatment%')
            ";
        $where = "WHERE

		-- n.visit_number = 2 AND
		n.is_signed_by_hcp IS TRUE AND
            -- n.effective_dateest < '2022-03-01' AND

		n.is_claim_closed IS NOT TRUE AND

	    n.is_cancelled IS NOT TRUE AND
            c.client_engagement_status_category <> 'DUMMY' AND
            c.name_first NOT ILIKE '%test%' AND
            c.name_last NOT ILIKE '%test%' AND
            n.id IN (SELECT note_id FROM bill WHERE code ILIKE '%treatment%' AND bill.is_cancelled IS NOT TRUE AND note_id IS NOT NULL)
		    -- AND
            -- n.id NOT IN (SELECT note_id FROM claim WHERE note_id IS NOT NULL) AND
            ";
        $filters = [];

        if(trim($request->input('f_pb_primary'))) {
            $v = trim($request->input('f_pb_primary'));
            if($v === 'yes') {
                $filters[] = "(c.is_part_b_primary = 'YES')";
            }
            else if($v === 'no') {
                $filters[] = "(c.is_part_b_primary != 'YES')";
            }
        }
        if(trim($request->input('f_deductible_zero'))) {
            $v = trim($request->input('f_deductible_zero'));
            if($v === 'yes') {
                $filters[] = "(c.mpb_remaining = 0)";
            }
            else if($v === 'no') {
                $filters[] = "(c.mpb_remaining > 0)";
            }
        }
        if(trim($request->input('f_note_hcp'))) {
            $v = trim($request->input('f_note_hcp'));
            $filters[] = "(n.hcp_pro_id IN (SELECT id FROM pro WHERE uid = '{$v}'))";
        }
        if(trim($request->input('f_2022_patient'))) {
            $v = trim($request->input('f_2022_patient'));
            if($v === 'yes') {
                $filters[] = "(c.created_at::DATE >= '2022-01-01'::DATE)";
            }
            else if($v === 'no') {
                $filters[] = "(c.created_at::DATE < '2022-01-01'::DATE)";
            }
        }

        if(count($filters)) {
            $filters = 'AND ' . implode(' AND ',  $filters);
        }
        else {
            $filters = '';
        }
        // $filters = '';
        $orderBy = "ORDER BY
			-- c.id ASC,
		n.id ASC, n.effective_dateest ASC";

        $countQuery = "SELECT count(*) {$from} {$where} {$filters}";
        // dd($countQuery);
        $countResult = DB::select($countQuery);
        $total = $countResult[0]->count;

        $defaultPageSize = 50;

        $page = $request->input('page') ?: 1;
        $perPage = $request->input('per_page') ?: $defaultPageSize;
        $offset = ($page - 1) * $perPage;

        $dataQuery = "SELECT {$columns} {$from} {$where} {$filters} {$orderBy} OFFSET {$offset} LIMIT {$perPage}";
        $rows = DB::select($dataQuery);

        $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
        $paginator->setPath(route('practice-management.notes-resolution-center-v2'));

        return view('app.practice-management.notes-resolution-center-v2', compact('rows', 'paginator'));
    }

    public function coverages(Request $request) {

        $columns = "(c.name_first || ' ' || c.name_last) as client_name,
            c.id,
            c.uid,
            c.created_at,
            c.sex,
            c.dob,
            c.age_in_years,
            row_to_json(lcpc.*) as latest_client_primary_coverage,
            row_to_json(lacpc.*) as latest_auto_client_primary_coverage,
            row_to_json(lmcpc.*) as latest_manual_client_primary_coverage,
            row_to_json(lmcpc_payer.*) as latest_manual_client_primary_coverage_payer
            ";
        $from = "FROM client AS c
            LEFT JOIN client_primary_coverage lcpc on c.effective_client_primary_coverage_id = lcpc.id
            LEFT JOIN client_primary_coverage lacpc on c.latest_auto_refresh_client_primary_coverage_id = lacpc.id
            LEFT JOIN client_primary_coverage lmcpc on c.latest_manual_client_primary_coverage_id = lmcpc.id
            LEFT JOIN payer lmcpc_payer on lmcpc.commercial_payer_id = lmcpc_payer.id
            ";
        $where = "WHERE
            (c.client_engagement_status_category IS NULL OR client_engagement_status_category <> 'DUMMY') AND
            c.name_first NOT ILIKE '%test%' AND
            c.name_last NOT ILIKE '%test%' AND
            c.created_at::DATE >= '2022-01-01'::DATE
             -- lmcpc.id IS NOT NULL ANd lmcpc.plan_type != 'MEDICARE'
            ";
        $filters = [];

        if(trim($request->input('f_name'))) {
            $v = trim($request->input('f_name'));
            $filters[] = "(c.name_first ILIKE '%{$v}%' OR c.name_last ILIKE '%{$v}%')";
        }
        if(trim($request->input('f_covered'))) {
            $v = trim($request->input('f_covered'));
            if($v !== 'any') {
                switch($v) {
                    case 'covered':
                        $filters[] = "(
                        (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.manual_determination_category = 'COVERED') OR
                        ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'YES')
                        )";
                        break;
                    case 'not-covered':
                        $filters[] = "(
                        (lmcpc.id IS NULL OR (lmcpc.is_cancelled IS TRUE OR lmcpc.manual_determination_category = 'NOT_COVERED')) AND
                        (lacpc.id IS NULL OR (lacpc.is_cancelled IS TRUE OR lacpc.auto_medicare_is_partbprimary = 'NO')) AND
                        (lmcpc.id IS NOT NULL OR lacpc.id IS NOT NULL)
                        )";
                        break;
                    case 'unknown':
                        $filters[] = "(
                        (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.manual_determination_category = 'UNKNOWN') OR
                        ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'UNKNOWN')
                        )";
                        break;
                }
            }
        }
        if(trim($request->input('f_covered'))) {
            $v = trim($request->input('f_covered'));
            if($v !== 'any') {
                switch($v) {
                    case 'covered':
                        $filters[] = "(
                (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.manual_determination_category = 'COVERED') OR
                ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'YES')
                )";
                        break;
                    case 'not-covered':
                        $filters[] = "(
                (lmcpc.id IS NULL OR (lmcpc.is_cancelled IS TRUE OR lmcpc.manual_determination_category = 'NOT_COVERED')) AND
                (lacpc.id IS NULL OR (lacpc.is_cancelled IS TRUE OR lacpc.auto_medicare_is_partbprimary = 'NO')) AND
                (lmcpc.id IS NOT NULL OR lacpc.id IS NOT NULL)
                )";
                        break;
                    case 'unknown':
                        $filters[] = "(
                (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.manual_determination_category = 'UNKNOWN') OR
                ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'UNKNOWN')
                )";
                        break;
                }
            }
        }
        if(trim($request->input('f_plan_type'))) {
            $v = strtoupper(trim($request->input('f_plan_type')));
            if($v !== 'ANY') {
                $filters[] = "(
                (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.plan_type = '{$v}') OR
                ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.plan_type = '{$v}')
                )";
            }
        }
        if(trim($request->input('f_pb_primary'))) {
            $v = trim($request->input('f_pb_primary'));
            if($v !== 'any') {
                switch($v) {
                    case 'primary':
                        $filters[] = "(
                (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.is_partbprimary = 'YES') OR
                ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'YES')
                )";
                        break;
                    case 'not-primary':
                        $filters[] = "(
                (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.is_partbprimary = 'NO') OR
                ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'NO')
                )";
                        break;
                    case 'unknown':
                        $filters[] = "(
                (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.is_partbprimary = 'UNKNOWN') OR
                ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.auto_medicare_is_partbprimary = 'UNKNOWN')
                )";
                        break;
                }
            }
        }
        if(trim($request->input('f_pb_active'))) {
            $v = trim($request->input('f_pb_active'));
            // TODO
        }
        if(trim($request->input('f_pc_active'))) {
            $v = trim($request->input('f_pc_active'));
            // TODO
        }
        if(trim($request->input('f_comm_payer'))) {
            $v = trim($request->input('f_comm_payer'));
            $filters[] = "(lmcpc_payer.name ILIKE '%{$v}%')";
        }
        if(trim($request->input('f_comm_member_id'))) {
            $v = trim($request->input('f_comm_member_id'));
            if($v !== 'any') {
                $filters[] = "(
                (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.commercial_member_identifier ILIKE '%{$v}%') OR
                ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.commercial_member_identifier ILIKE '%{$v}%')
                )";
            }
        }
        if(trim($request->input('f_comm_group_num'))) {
            $v = trim($request->input('f_comm_group_num'));
            if($v !== 'any') {
                $filters[] = "(
                (lmcpc.id IS NOT NULL AND lmcpc.is_cancelled IS NOT TRUE AND lmcpc.commercial_group_number ILIKE '%{$v}%') OR
                ((lmcpc.id IS NULL OR lmcpc.is_cancelled IS TRUE) AND lacpc.id IS NOT NULL AND lacpc.is_cancelled IS NOT TRUE AND lacpc.commercial_group_number ILIKE '%{$v}%')
                )";
            }
        }

        if(count($filters)) {
            $filters = 'AND ' . implode(' AND ',  $filters);
        }
        else {
            $filters = '';
        }
        // $filters = '';
        $orderBy = "ORDER BY client_name ASC";

        $countQuery = "SELECT count(*) {$from} {$where} {$filters}";
        // dd($countQuery);
        $countResult = DB::select($countQuery);
        $total = $countResult[0]->count;

        $defaultPageSize = 10;

        $page = $request->input('page') ?: 1;
        $perPage = $request->input('per_page') ?: $defaultPageSize;
        $offset = ($page - 1) * $perPage;

        $dataQuery = "SELECT {$columns} {$from} {$where} {$filters} {$orderBy} OFFSET {$offset} LIMIT {$perPage}";
        $rows = DB::select($dataQuery);

        $paginator = new LengthAwarePaginator($rows, $total, $request->input('per_page') ?: $defaultPageSize, $request->input('page') ?: 1);
        $paginator->setPath(route('practice-management.coverages'));

        // dd($rows);

        return view('app.practice-management.coverages', compact('rows', 'paginator'));
    }

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

        $reviewRequests = ClientReviewRequest::where('status', '!=', 'DONE');
        if($pro->pro_type !== 'ADMIN'){
            $reviewRequests = $reviewRequests->where('pro_id', $pro->id);
        }
        $reviewRequests = $reviewRequests->orderBy('created_at', 'DESC')->paginate(50);
        return view('app.ps.review-requests.list', compact('reviewRequests'));
    }


}