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\Handout;
use App\Models\MBClaim;
use App\Models\Measurement;
use App\Models\Bill;
use App\Models\Claim;
use App\Models\Client;
use App\Models\McpRequest;
use App\Models\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\ProTextShortcut;
use App\Models\ProTransaction;
use App\Models\Shipment;
use App\Models\SupplyOrder;
use App\Models\Ticket;
use App\Models\ClientMeasurementDaysPerMonth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Http;
use PDF;
use DateTime;
use DateTimeZone;
use Illuminate\Http\Request;

class PracticeManagementController extends Controller
{

    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();
		return view ('app.practice-management.remote-monitoring-report', compact('rows'));
	}

    public function billingReport(Request $request)
    {
        $rows = BillingReport::paginate(50);
        return view('app.practice-management.billing-report', compact('rows'));
    }

    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)
    {
        $transactions = ProTransaction::where('pro_id', $this->performer()->pro->id)->orderBy('created_at', 'desc')->get();
        return view('app.practice-management.financial-transactions', compact('transactions'));
    }

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

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

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

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

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

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

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

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

                break;

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

    public function naBillableSignedNotes(Request $request)
    {

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

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

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

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

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

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

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

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

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

    public function myTextShortcuts(Request $request)
    {

        $myTextShortcuts = 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') {
            $myTextShortcuts = $myTextShortcuts->where('pro_id', $this->performer()->pro_id);
        }

        $myTextShortcuts = $myTextShortcuts
            ->orderBy('pro.name_last')
            ->orderBy('pro.name_first')
            ->get();

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

    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
            ->orderBy('category', 'asc')
            ->orderBy('position_index', 'asc')
            ->get();
        return view('app.practice-management.my-favorites', compact('myFavorites', '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);
    }

    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;
        $allPros = [];
        $expectedForHcp = null;
        if ($performerPro->pro_type == 'ADMIN') {
            $allPros = Pro::all();
            $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(100);
        } else {
            $clients = Client::orderBy('created_at', 'desc')->paginate(100);
        }
        return view('app.practice-management.cellular-device-manager', compact('clients', 'allPros', '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('is_signed_by_hcp', true);
            if ($targetPro) {
                $bills = $bills->where('hcp_pro_id', $targetPro->id);
            }
        }
        else if($request->input('t') === 'na') {
            $bills = $bills
                ->where('has_generic_pro_been_paid', false)
                ->where('is_signed_by_generic_pro', true);
            if ($targetPro) {
                $bills = $bills->where('generic_pro_id', $targetPro->id);
            }
        }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    public function remoteMonitoring(Request $request) {

        $performer = $this->performer();

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

        // filters
        $filters = '';
        $fmd = $request->input('fmd');
        if($fmd && $fmd !== 'all') {
            switch($fmd) {
                case 'lt16':
                    $filters .= ' AND (care_month.number_of_days_with_remote_measurements < 16 OR care_month.number_of_days_with_remote_measurements IS NULL) ';
                    break;
                case 'gte16':
                    $filters .= ' AND (care_month.number_of_days_with_remote_measurements >= 16 AND care_month.number_of_days_with_remote_measurements IS NOT NULL) ';
                    break;
            }
        }
        $fcomm = $request->input('fcomm');
        if($fcomm && $fcomm !== 'all') {
            switch($fcomm) {
                case 'not-done':
                    $filters .= ' AND (care_month.has_non_hcp_communicated_to_patient_about_rm = FALSE OR care_month.has_non_hcp_communicated_to_patient_about_rm IS NULL) ';
                    break;
                case 'done':
                    $filters .= ' AND (care_month.has_non_hcp_communicated_to_patient_about_rm = TRUE AND care_month.has_non_hcp_communicated_to_patient_about_rm IS NOT NULL) ';
                    break;
            }
        }
        $c = $request->input('c');
        if($c) {
            $filters .= ' AND client.uid = \'' . $request->input('c') . '\' ';
        }

        $patients = DB::select(
            DB::raw(
                "
SELECT client.name_first, client.name_last, client.uid as client_uid,
       care_month.uid as care_month_uid,
       care_month.id as care_month_id,
       care_month.start_date,
       care_month.number_of_days_with_remote_measurements,
       care_month.has_non_hcp_communicated_to_patient_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,
       client.mcp_pro_id,
       client.default_na_pro_id,
       client.rmm_pro_id,
       client.rme_pro_id,
       client.cell_number
FROM care_month join client on care_month.client_id = client.id
WHERE (
          (client.mcp_pro_id = {$performer->pro->id})
          OR (client.rmm_pro_id = {$performer->pro->id})
          OR (client.rme_pro_id = {$performer->pro->id})
          OR (client.default_na_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())') . "
  {$filters}
ORDER BY care_month.number_of_days_with_remote_measurements DESC NULLS LAST, client.name_first ASC, client.name_last ASC
"
            )
        );

        $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 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->is_part_b_primary == '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.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,
       -- 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 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'))
-- 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 ASC
--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 processNotes(Request  $request) {

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

        $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')
                ->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")
                ->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', 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(),
            // 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 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:
                $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', false)
                    ->orderBy('effective_dateest', 'DESC')
                    ->first();
                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', 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;
        }
        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)->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');
    }

}