stagfe2/app/Http/Controllers/PracticeManagementController.php

<?php

namespace App\Http\Controllers;

use App\Models\AppSession;
use App\Models\ClaimEDI;
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\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 Illuminate\Support\Facades\DB;
use PDF;
use DateTime;
use DateTimeZone;
use Illuminate\Http\Request;

class PracticeManagementController extends Controller
{
    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;

            // 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 bills(Request $request, $filter = '')
    {
        $proID = $this->performer()->pro->id;
        $query = Bill::where('is_cancelled', false);
        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 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)
    {
        $performer = $this->performer();
        $myTextShortcuts = ProTextShortcut::where('pro_id', $performer->pro_id)->where('is_removed', false)->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;
        $allPros = [];
        if ($performerPro->pro_type == 'ADMIN') {
            $allPros = Pro::all();
            $targetPro = Pro::where('uid', $proUid)->first();
        } else {
            $targetPro = $performerPro;
        }
        $bills = [];
        if ($targetPro) {
            $bills = Bill::where('hcp_pro_id', $targetPro->id)->
            where('has_hcp_been_paid', false)->
            where('is_cancelled', false)->
            where('is_signed_by_hcp', true)->
            orderBy('effective_date', 'desc')->paginate();
        } else {
            $bills = Bill::where('has_hcp_been_paid', false)->
            where('is_cancelled', false)->
            where('is_signed_by_hcp', true)->
            orderBy('effective_date', 'desc')->
            paginate();
        }
        $viewData = [
            'bills' => $bills,
            'allPros' => $allPros,
            '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);
        }

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

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

    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')->paginate();

        //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'));
    }

    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()
        ];
    }

    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 claimsReport(Request $request){
        return view('app.practice-management.claims-report');
    }

}