stagfe2/app/Models/Pro.php

<?php

namespace App\Models;

# use Illuminate\Database\Eloquent\Model;

use App\Helpers\TimeLine;
use DateTime;
use Exception;
use Illuminate\Support\Facades\DB;

class Pro extends Model
{
    protected $table = 'pro';

    public function displayName() {
        $name = [];
        if(!empty($this->name_display)) return $this->name_display;
        if(!empty($this->name_last)) $name[] = $this->name_last;
        if(!empty($this->name_first)) $name[] = $this->name_first;
        if(!count($name)) {
            $name = $this->name_display;
        }
        else {
            $name = implode(", ", $name);
        }
        return $name;
    }

    public function initials() {
        $characters = [];
        if(!empty($this->name_first)) $characters[] = $this->name_first[0];
        if(!empty($this->name_last)) $characters[] = $this->name_last[0];
        return strtolower(implode("", $characters));
    }

    public function debitBills()
    {
        return $this->hasMany(Bill::class, 'debit_pro_id');
    }

    public function cmBills()
    {
        return $this->hasMany(Bill::class, 'cm_pro_id');
    }


    public function hcpBills()
    {
        return $this->hasMany(Bill::class, 'hcp_pro_id');
    }

    public function teamsWhereAssistant()
    {
        return $this->hasMany(ProTeam::class, 'assistant_pro_id', 'id');
    }

    public function isDefaultNA()
    {
       return $this->is_considered_for_dna;
    }

    public function lastPayment() {
        return ProTransaction
            ::where('pro_id', $this->id)
            ->where('plus_or_minus', 'PLUS')
            ->orderBy('created_at', 'desc')
            ->first();
    }

    public function hasRates() {
        $numRates = ProRate::where('is_active', true)->where('pro_id', $this->id)->count();
        return $numRates > 0;
    }

    public function cmRates() {
        return ProRate::distinct('code')
            ->where('is_active', true)
            ->where('pro_id', $this->id)
            ->where('code', 'LIKE', 'CM%')
            ->get();
    }

    public function rmRates() {
        return ProRate::distinct('code')
            ->where('is_active', true)
            ->where('pro_id', $this->id)
            ->where('code', 'LIKE', 'RM%')
            ->get();
    }

    public function noteRates() {
        return ProRate::distinct('code')
            ->where('is_active', true)
            ->where('pro_id', $this->id)
            ->where('code', 'NOT LIKE', 'CM%')
            ->where('code', 'NOT LIKE', 'RM%')
            ->where('responsibility', '<>', 'GENERIC')
            ->get();
    }

    public function genericRates() {
        return ProRate::distinct('code')
            ->where('is_active', true)
            ->where('pro_id', $this->id)
            ->where('responsibility', 'GENERIC')
            ->get();
    }

    public function recentDebits() {
        return ProTransaction
            ::where('pro_id', $this->id)
            ->where('plus_or_minus', 'PLUS')
            ->orderBy('created_at', 'desc')
            ->skip(0)->take(4)->get();
    }

    public function shortcuts() {
        return $this->hasMany(ProTextShortcut::class, 'pro_id')->where('is_removed', false);
    }

    public function allShortcuts() {
        $myId = $this->id;
        $shortcuts = ProTextShortcut::where('is_removed', false)
            ->where(function ($query2) use ($myId) {
                $query2
                    ->where('pro_id', $myId)
                    ->orWhereNull('pro_id');
            })
            ->orderBy('shortcut')
            ->get();
        return $shortcuts;
    }

    public function noteTemplates() {
        return $this->hasMany(NoteTemplatePro::class, 'pro_id')
            ->where('is_removed', false)
            ->orderBy('position_index', 'asc');
    }

    public function visitTemplates() {
        //TODO: use visit access
        return VisitTemplate::where('is_active', true)->get();
    }

    public function currentWork() {
        return ProClientWork::where('pro_id', $this->id)->where('is_active', true)->first();
    }

    public function isWorkingOnClient($_client) {
        $count = ProClientWork::where('pro_id', $this->id)->where('client_id', $_client->id)->where('is_active', true)->count();
        return $count > 0;
    }

    public function canvasCustomItems($_key) {
        return ClientCanvasDataCustomItem::where('key', $_key)
            ->where('pro_id', $this->id)
            ->where('is_removed', false)
            ->orderBy('label')
            ->get();
    }


    /**
     * @param $_start - YYYY-MM-DD
     * @param $_end - YYYY-MM-DD
     * @param string $_timezone - defaults to EASTERN
     * @param string $_availableBG - defaults to #00a
     * @param string $_unavailableBG - defaults to #a00
     * @return array
     * @throws Exception
     */
    public function getAvailabilityEvents($_start, $_end, $_timezone = 'EASTERN', $_availableBG = '#00a', $_unavailableBG = '#a00') {

        $_start .= ' 00:00:00';
        $_end .= ' 23:59:59';

        // get availability data
        $proGenAvail = ProGeneralAvailability
            ::where('is_cancelled', false)
            ->where('pro_id', $this->id)
            ->get();
        $proSpecAvail = ProSpecificAvailability
            ::where('is_cancelled', false)
            ->where('pro_id', $this->id)
            ->where(function ($query) use ($_start, $_end) {
                $query
                    ->where(function ($query2) use ($_start, $_end) {
                        $query2
                            ->where('start_time', '>=', $_start)
                            ->where('start_time', '<=', $_end);
                    })
                    ->orWhere(function ($query2) use ($_start, $_end) {
                        $query2
                            ->where('end_time', '>=', $_start)
                            ->where('end_time', '<=', $_end);
                    });
            })
            ->get();
        $proSpecUnavail = ProSpecificUnavailability
            ::where('is_cancelled', false)
            ->where('pro_id', $this->id)
            ->where(function ($query) use ($_start, $_end) {
                $query
                    ->where(function ($query2) use ($_start, $_end) {
                        $query2
                            ->where('start_time', '>=', $_start)
                            ->where('start_time', '<=', $_end);
                    })
                    ->orWhere(function ($query2) use ($_start, $_end) {
                        $query2
                            ->where('end_time', '>=', $_start)
                            ->where('end_time', '<=', $_end);
                    });
            })
            ->get();

        // default GA
        // if no gen avail, assume mon to fri, 9 to 7
        /*if (count($proGenAvail) === 0) {
            $dayNames = ['MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY'];
            foreach ($dayNames as $dayName) {
                $item = new \stdClass();
                $item->day_of_week = $dayName;
                $item->timezone = $_timezone;
                $item->start_time = '09:00:00';
                $item->end_time = '18:00:00';
                $proGenAvail->push($item);
            }
        }*/

        // create timeline
        $phpTZ = appTZtoPHPTZ($_timezone);
        $phpTZObject = new \DateTimeZone($phpTZ);
        $startDate = new \DateTime($_start, $phpTZObject);
        $endDate = new \DateTime($_end, $phpTZObject);
        $proTimeLine = new TimeLine($startDate, $endDate);

        // General availability
        $period = new \DatePeriod($startDate, \DateInterval::createFromDateString('1 day'), $endDate);
        $days = [];
        foreach ($period as $day) {
            $days[] = [
                "day" => strtoupper($day->format("l")), // SUNDAY, etc.
                "date" => $day->format("Y-m-d"), // 2020-10-04, etc.
            ];
        }
        foreach ($days as $day) {
            $proGenAvailForTheDay = $proGenAvail->filter(function ($record) use ($day) {
                return $record->day_of_week === $day["day"];
            });
            foreach ($proGenAvailForTheDay as $ga) {
                $gaStart = new \DateTime($day["date"], new \DateTimeZone(appTZtoPHPTZ($ga->timezone)));
                $parts = explode(":", $ga->start_time);
                $gaStart->setTime(intval($parts[0]), intval($parts[1]), intval($parts[2]));
                $gaStart->setTimezone($phpTZObject);
                $gaEnd = new \DateTime($day["date"], new \DateTimeZone(appTZtoPHPTZ($ga->timezone)));
                $parts = explode(":", $ga->end_time);
                $gaEnd->setTime(intval($parts[0]), intval($parts[1]), intval($parts[2]));
                $gaEnd->setTimezone($phpTZObject);
                $proTimeLine->addAvailability($gaStart, $gaEnd);
            }
        }

        // specific availability
        foreach ($proSpecAvail as $sa) {
            $saStart = new \DateTime($sa->start_time, new \DateTimeZone(appTZtoPHPTZ($sa->timezone)));
            $saStart->setTimezone($phpTZObject);
            $saEnd = new \DateTime($sa->end_time, new \DateTimeZone(appTZtoPHPTZ($sa->timezone)));
            $saEnd->setTimezone($phpTZObject);
            $proTimeLine->addAvailability($saStart, $saEnd);
        }

        // specific unavailability
        foreach ($proSpecUnavail as $sua) {
            $suaStart = new \DateTime($sua->start_time, new \DateTimeZone(appTZtoPHPTZ($sua->timezone)));
            $suaStart->setTimezone($phpTZObject);
            $suaEnd = new \DateTime($sua->end_time, new \DateTimeZone(appTZtoPHPTZ($sua->timezone)));
            $suaEnd->setTimezone($phpTZObject);
            $proTimeLine->removeAvailability($suaStart, $suaEnd);
        }

        $events = [];

        // availability
        foreach ($proTimeLine->getAvailable() as $item) {
            $eStart = new \DateTime('@' . $item->start);
            $eStart->setTimezone($phpTZObject);
            $eEnd = new \DateTime('@' . $item->end);
            $eEnd->setTimezone($phpTZObject);
            $events[] = [
                "type" => "availability",
                "start" => $eStart->format('Y-m-d H:i:s'),
                "end" => $eEnd->format('Y-m-d H:i:s'),
                "editable" => false,
                "backgroundColor" => $_availableBG
            ];
        }

        // unavailability
        foreach ($proTimeLine->getUnavailable() as $item) {
            $eStart = new \DateTime('@' . $item->start);
            $eStart->setTimezone($phpTZObject);
            $eEnd = new \DateTime('@' . $item->end);
            $eEnd->setTimezone($phpTZObject);
            $events[] = [
                "type" => "unavailability",
                "start" => $eStart->format('Y-m-d H:i:s'),
                "end" => $eEnd->format('Y-m-d H:i:s'),
                "editable" => false,
                "backgroundColor" => $_unavailableBG
            ];
        }

        return $events;

    }

    public function getMyClientIds($_search = false) {
        $clients = $this->getAccessibleClientsQuery($_search)->get();
        $clientIds = [];
        foreach($clients as $client){
            $clientIds[] = $client->id;
        }
        return $clientIds;
    }

    public function favoritesByCategory($_category) {
        return ProFavorite::where('pro_id', $this->id)
            ->where('is_removed', false)
            ->where('category', $_category)
            ->orderBy('category', 'asc')
            ->orderBy('position_index', 'asc')
            ->get();
    }

    public function favoritesByCategoryDecoded($_category) {
        $favorites = ProFavorite::where('pro_id', $this->id)
            ->where('is_removed', false)
            ->where('category', $_category)
            ->orderBy('category', 'asc')
            ->orderBy('position_index', 'asc')
            ->get();
        foreach ($favorites as $favorite) {
            if ($favorite->data) {
                $favorite->data = json_decode($favorite->data);
            }
        }
        return $favorites;
    }

    function get_patients_count_as_mcp() {
        $query = Client::whereNull('shadow_pro_id');
        return $query->where('mcp_pro_id', $this->id)->count();
    }

    function get_new_patients_awaiting_visit_count_as_mcp() {
        $query = Client::whereNull('shadow_pro_id');
        return $query->where('mcp_pro_id', $this->id)
            ->where('has_mcp_done_onboarding_visit', '!=', 'YES')
            ->count();
    }

    function get_notes_pending_signature_count_as_mcp() {
        return Note::where('hcp_pro_id', $this->id)
            ->where('is_cancelled', '<>', true)
            ->where('is_core_note', '<>', true)
            ->where('is_signed_by_hcp', '<>', true)
            ->count();
    }

    function get_notes_pending_summary_suggestion_as_mcp_query(){
        $segmentsWithProposedSummarySuggestion = Segment::whereNotNull('proposed_segment_summary_suggestion_id')->get();
        $noteIDs = [];
        foreach($segmentsWithProposedSummarySuggestion as $seg){
            $noteIDs[] = $seg->note_id;
        }
        return Note::where('hcp_pro_id', $this->id)
            ->where('is_cancelled', '<>', true)
            ->where('is_core_note', '<>', true)
            ->where('is_signed_by_hcp', true)
            ->whereIn('id', $noteIDs);
    }

    function get_notes_pending_summary_suggestion_count_as_mcp(){
        return $this->get_notes_pending_summary_suggestion_as_mcp_query()->count();
    }

    function get_notes_pending_summary_suggestion_as_mcp(){
        return $this->get_notes_pending_summary_suggestion_as_mcp_query()->get();
    }

    function get_notes_pending_summary_suggestion_as_admin_query(){
        $segmentsWithProposedSummarySuggestion = Segment::whereNotNull('proposed_segment_summary_suggestion_id')->get();
        $noteIDs = [];
        foreach($segmentsWithProposedSummarySuggestion as $seg){
            $noteIDs[] = $seg->note_id;
        }
        return Note::where('is_cancelled', '<>', true)
            ->where('is_core_note', '<>', true)
            ->where('is_signed_by_hcp', true)
            ->whereIn('id', $noteIDs);
    }

    function get_notes_pending_summary_suggestion_count_as_admin(){
        return $this->get_notes_pending_summary_suggestion_as_admin_query()->count();
    }

    function get_notes_pending_summary_suggestion_as_admin(){
        return $this->get_notes_pending_summary_suggestion_as_admin_query()->get();
    }

    function get_notes_pending_billing_count_as_mcp() {
        return Note::where('hcp_pro_id', $this->id)
            ->where('is_cancelled', '<>', true)
            ->where('is_signed_by_hcp', true)
            ->where('is_billing_marked_done', '<>', true)
            ->count();
    }


    function get_measurements_awaiting_review_count_as_mcp() {
        $result = DB::select(DB::raw("
SELECT SUM(rm_num_measurements_not_stamped_by_mcp) AS count
FROM care_month
WHERE mcp_pro_id = :pro_id
  AND rm_num_measurements_not_stamped_by_mcp IS NOT NULL
  AND rm_num_measurements_not_stamped_by_mcp > 0;
        "), ["pro_id" => $this->id]);
        if($result) return $result[0]->count;
    }

    function get_bills_pending_signature_count_as_mcp(){
        $pendingBillsToSign = Bill::where('bill_service_type', '<>', 'CARE_MONTH')->where(function ($query) {
            $query->where('hcp_pro_id', $this->id)->where('is_signed_by_hcp', false)->where('is_cancelled', false);
        })
            ->orWhere(function ($query) {
                $query->where('cm_pro_id', $this->id)->where('is_signed_by_cm', false)->where('is_cancelled', false);
            })->orWhere(function ($query) {
                $query->where('rme_pro_id', $this->id)->where('is_signed_by_rme', false)->where('is_cancelled', false);
            })->orWhere(function ($query) {
                $query->where('rmm_pro_id', $this->id)->where('is_signed_by_rmm', false)->where('is_cancelled', false);
            })->orWhere(function ($query) {
                $query->where('generic_pro_id', $this->id)->where('is_signed_by_generic_pro', false)->where('is_cancelled', false);
            })->count();

        return $pendingBillsToSign;
    }

    function get_incoming_reports_pending_signature_count_as_mcp() {
        return IncomingReport::where('hcp_pro_id', $this->id)
            ->where('has_hcp_pro_signed', '<>', true)
            ->where('is_entry_error', '<>', true)
            ->count();
    }

    function get_patients_without_appointment_query() {
        return Client::where('mcp_pro_id', $this->id)
                    ->whereNull('today_mcp_appointment_date')
                    ->where(function($q){
                        $q->whereNull('next_mcp_appointment_id')
                        ->orWhere('next_mcp_appointment_date', '<=', DB::raw('NOW()::DATE'));
                    });
    }

    function get_patients_without_appointment_for_dna_query() {
        return Client::where('default_na_pro_id', $this->id)
            ->whereNull('today_mcp_appointment_date')
            ->where(function($q){
                $q->whereNull('next_mcp_appointment_id')
                    ->orWhere('next_mcp_appointment_date', '<=', DB::raw('NOW()::DATE'));
            });
    }

    function get_patients_without_appointment_count_as_mcp() {
        return $this->get_patients_without_appointment_query()->count();
    }

    function get_patients_overdue_for_visit_query() {
        return Client::where('mcp_pro_id', $this->id)
                    ->where(function($q){
                        $q->whereNull('most_recent_completed_mcp_note_id')
                        ->orWhere('most_recent_completed_mcp_note_date', '<', DB::raw("NOW()::DATE - INTERVAL '45 DAY'"));
                    });
    }

    function get_patients_overdue_for_visit_for_dna_query() {
        return Client::where('default_na_pro_id', $this->id)
            ->where(function($q){
                $q->whereNull('most_recent_completed_mcp_note_id')
                    ->orWhere('most_recent_completed_mcp_note_date', '<', DB::raw("NOW()::DATE - INTERVAL '45 DAY'"));
            });
    }

    function get_patients_overdue_count_as_mcp() {
        return $this->get_patients_overdue_for_visit_query()->count();
    }

    function get_patients_without_remote_measurement_in_48_hours_count_as_mcp() {
        return DB::select("SELECT COUNT(*) as count FROM client WHERE ((most_recent_cellular_measurement_at+ interval '48 hour')::timestamp < NOW() OR most_recent_cellular_measurement_id IS NULL) AND client.mcp_pro_id = :mcp_pro_id AND is_active IS TRUE", ['mcp_pro_id'=>$this->id])[0]->count; 
    }

    function get_cancelled_appointments_pending_acknowledgement_count_as_mcp_query() {
        // SELECT * FROM appointment WHERE hcp_pro_id = :me.id AND status = 'REJECTED' AND wasAcknowledgedByAppointmentPro IS NOT TRUE;
        return Appointment::where('pro_id', $this->id)
            ->where('latest_confirmation_decision_enum', 'REJECTED')
            ->where('is_decision_acknowledgement_from_appointment_pro_pending', true);
    }
    function get_cancelled_appointments_pending_acknowledgement_count_as_mcp() {
        // SELECT * FROM appointment WHERE hcp_pro_id = :me.id AND status = 'REJECTED' AND wasAcknowledgedByAppointmentPro IS NOT TRUE;
        return $this->get_cancelled_appointments_pending_acknowledgement_count_as_mcp_query()->count();
    }

    function get_cancelled_bills_awaiting_review_count_as_mcp() {
        // SELECT * FROM bill WHERE bill_service_type = 'NOTE' AND is_cancelled IS TRUE AND isCancellationAcknowledged IS FALSE;
        return Bill::where('hcp_pro_id', $this->id)
            ->where('bill_service_type', 'NOTE')
            ->where('is_cancelled', true)
            ->where('is_cancellation_acknowledged', '<>', true)
            ->count();
    }

    function get_cancelled_supply_orders_awaiting_review_count_as_mcp() {
        // SELECT * FROM supply_order WHERE signed_by_pro_id = :me.id AND is_cancelled IS TRUE AND isCancellationAcknowledged IS NOT TRUE;
        return SupplyOrder::where('signed_by_pro_id', $this->id)
            ->where('is_cancelled', true)
            ->where('is_cancellation_acknowledged', '<>', true)
            ->count();
    }

    function get_erx_and_orders_awaiting_signature_count_as_mcp() {
        // SELECT * FROM erx WHERE hcp_pro_id = :me.id AND pro_declared_status <> 'CANCELLED' AND hasHcpProSigned IS NOT TRUE;
        return Erx::where('hcp_pro_id', $this->id)
            ->where('pro_declared_status', '<>', 'CANCELLED')
            ->where('has_hcp_pro_signed', '<>', true)
            ->count();
    }

    function get_supply_orders_awaiting_signature_count_as_mcp() {
        // SELECT supply_order.id FROM supply_order WHERE signed_by_pro_id IS NOT TRUE AND is_cancelled IS NOT TRUE AND created_by_pro_id = :me.id;
        return SupplyOrder::where('created_by_pro_id', $this->id)
            ->whereNull('signed_by_pro_id')
            ->where('is_cancelled', '<>', true)
            ->count();
    }

    function get_supply_orders_awaiting_shipment_count_as_mcp() {
        return SupplyOrder::where('created_by_pro_id', $this->id)
            ->where('is_signed_by_pro', true)
            ->where('is_cleared_for_shipment', true)
            ->whereNull('shipment_id')
            ->count();
    }

    function get_birthdays_today_as_mcp(){
        return;
        $queryClients = $this->performer()->pro->getAccessibleClientsQuery();
        $keyNumbers['patientsHavingBirthdayToday'] = $queryClients
            ->whereRaw('EXTRACT(DAY from dob) = ?', [date('d')])
            ->whereRaw('EXTRACT(MONTH from dob) = ?', [date('m')])
            ->count();

        $reimbursement = [];
        $reimbursement["currentBalance"] =  $performer->pro->balance;
        $reimbursement["nextPaymentDate"] = '--';
        $lastPayment = ProTransaction::where('pro_id', $performerProID)->where('plus_or_minus', 'PLUS')->orderBy('created_at', 'DESC')->first();
        if ($lastPayment) {
            $reimbursement["lastPayment"] =  $lastPayment->amount;
            $reimbursement["lastPaymentDate"] = $lastPayment->created_at;
        } else {
            $reimbursement["lastPayment"] = '--';
            $reimbursement["lastPaymentDate"] = '--';
        }
    }

    public function getAppointmentsPendingStatusChangeAck() {
        return Appointment::where('pro_id', $this->id)
            ->where('is_status_acknowledgement_from_appointment_pro_pending', true)
            //->where('raw_date', '>=', DB::raw('NOW()'))
            ->orderBy('raw_date', 'asc')
            ->get();
    }
    public function getAppointmentsPendingDecisionAck() {
        return Appointment::where('pro_id', $this->id)
            ->where('is_decision_acknowledgement_from_appointment_pro_pending', true)
            //->where('raw_date', '>=', DB::raw('NOW()'))
            ->orderBy('raw_date', 'asc')
            ->get();
    }
    public function getAppointmentsPendingTimeChangeAck() {
        return Appointment::where('pro_id', $this->id)
            ->where('is_time_change_acknowledgement_from_appointment_pro_pending', true)
            //->where('raw_date', '>=', DB::raw('NOW()'))
            ->orderBy('raw_date', 'asc')
            ->get();
    }

    public function getAccessibleClientsQuery($_search = false) {
        $proID = $this->id;
        $query = Client::whereNull('shadow_pro_id');
        if ($this->pro_type === 'ADMIN' && ($_search ? $this->can_see_any_client_via_search : $this->can_see_all_clients_in_list)) {
            $query = $query->where('id', '>', 0);
        } else {
            $query = $query->where(function ($q) use ($proID) {
                $q->where('mcp_pro_id', $proID)
                    ->orWhere('cm_pro_id', $proID)
                    ->orWhere('rmm_pro_id', $proID)
                    ->orWhere('rme_pro_id', $proID)
                    ->orWhere('physician_pro_id', $proID)
                    ->orWhere('default_na_pro_id', $proID)
                    ->orWhereRaw('id IN (SELECT client_id FROM client_pro_access WHERE is_active AND pro_id = ?)', [$proID])
                    ->orWhereRaw('id IN (SELECT client_id FROM appointment WHERE status NOT IN (\'CANCELLED\') AND pro_id = ?)', [$proID])
                    ->orWhereRaw('id IN (SELECT mcp_pro_id FROM client_program WHERE client_id = client.id AND is_active = TRUE)')
                    ->orWhereRaw('id IN (SELECT manager_pro_id FROM client_program WHERE client_id = client.id AND is_active = TRUE)')
                    ->orWhereRaw('id IN (SELECT client_id FROM note WHERE ally_pro_id = ? AND is_cancelled = FALSE)', [$proID]);;
            });
        }
        return $query;
    }

    public function canAccess($_patientUid) {
        $proID = $this->id;
        if ($this->pro_type === 'ADMIN') {
            return true;
        }
        $canAccess = Client::select('uid')
            ->where('uid', $_patientUid)
            ->where(function ($q) use ($proID) {
                $q->whereNull('mcp_pro_id')
                    ->orWhere('mcp_pro_id', $proID)
                    ->orWhere('cm_pro_id', $proID)
                    ->orWhere('rmm_pro_id', $proID)
                    ->orWhere('rme_pro_id', $proID)
                    ->orWhere('physician_pro_id', $proID)
                    ->orWhere('default_na_pro_id', $proID)
                    ->orWhereRaw('id IN (SELECT client_id FROM client_pro_access WHERE is_active AND pro_id = ?)', [$proID])
                    ->orWhereRaw('id IN (SELECT client_id FROM appointment WHERE status NOT IN (\'CANCELLED\') AND pro_id = ?)', [$proID])
                    ->orWhereRaw('id IN (SELECT mcp_pro_id FROM client_program WHERE client_id = client.id AND is_active = TRUE)')
                    ->orWhereRaw('id IN (SELECT manager_pro_id FROM client_program WHERE client_id = client.id AND is_active = TRUE)')
                    ->orWhereRaw('id IN (SELECT client_id FROM note WHERE ally_pro_id = ? AND is_cancelled = FALSE)', [$proID]);
            })->count();
        return !!$canAccess;
    }

    public function canAddCPMEntryForMeasurement(Measurement $measurement, Pro $pro)
    {
        // check if client has any programs where this measurement type is allowed
        $allowed = false;
        $client = $measurement->client;
        $clientPrograms = $client->clientPrograms;

        if($pro->pro_type !== 'ADMIN') {
            $clientPrograms = $clientPrograms->filter(function($_clientProgram) use ($pro) {
                return  $_clientProgram->manager_pro_id === $pro->id;
            });
        }

        if(count($clientPrograms)) {
            foreach ($clientPrograms as $clientProgram) {
                if(strpos(strtolower($clientProgram->measurement_labels), '|' . strtolower($measurement->label) . '|') !== FALSE) {
                    $allowed = true;
                    break;
                }
            }
        }

        return $allowed ? $clientPrograms : FALSE;
    }

    public function getUnstampedMeasurementsFromCurrentMonth($_countOnly, $skip, $limit)
    {

        date_default_timezone_set('US/Eastern');

        $start = strtotime(date('Y-m-01'));
        $end = date_add(date_create(date('Y-m-01')), date_interval_create_from_date_string("1 month"))->getTimestamp();
        $start *= 1000;
        $end *= 1000;

        $measurementsQuery = Measurement::where('measurement.is_active', true)
            ->join('client', 'client.id', '=', 'measurement.client_id')
            ->whereNotNull('measurement.client_bdt_measurement_id')
            ->whereNotNull('measurement.ts')
            ->where('measurement.is_cellular_zero', false)
            ->where('measurement.ts', '>=', $start)
            ->where('measurement.ts', '<', $end)
            ->whereIn('measurement.client_id', $this->getMyClientIds())
            ->where(function ($q) {
                $q
                    ->where(function ($q2) {
                        $q2
                            ->where('client.mcp_pro_id', $this->id)
                            ->where('measurement.has_been_stamped_by_mcp', false);
                    })
                    ->orWhere(function ($q2) {
                        $q2
                            ->where('client.default_na_pro_id', $this->id)
                            ->where('measurement.has_been_stamped_by_non_hcp', false);
                    })
                    ->orWhere(function ($q2) {
                        $q2
                            ->where('client.rmm_pro_id', $this->id)
                            ->where('measurement.has_been_stamped_by_rmm', false);
                    })
                    ->orWhere(function ($q2) {
                        $q2
                            ->where('client.rme_pro_id', $this->id)
                            ->where('measurement.has_been_stamped_by_rme', false);
                    });
            });

        if($_countOnly) {
            return $measurementsQuery->count();
        }

        $x = [];
        $measurements = $measurementsQuery
            ->orderBy('ts', 'desc')
            ->skip($skip)
            ->take($limit)
            ->get();

        // eager load stuff needed in JS
        foreach ($measurements as $measurement) {
//            if ($measurement->client_bdt_measurement_id) {
//                $measurement->bdtMeasurement = $measurement->clientBDTMeasurement->measurement;
//            }
            unset($measurement->clientBDTMeasurement); // we do not need this travelling to the frontend

            $client = [
                "uid" => $measurement->client->uid,
                "name" => $measurement->client->displayName(),
            ];
            $measurement->patient = $client;

            $measurement->careMonth = $measurement->client->currentCareMonth();
            $measurement->timestamp = friendlier_date_time($measurement->created_at);

            unset($measurement->client); // we do not need this travelling to the frontend

            if(@$measurement->detail_json) unset($measurement->detail_json);
            if(@$measurement->canvas_data) unset($measurement->canvas_data);
            if(@$measurement->latest_measurements) unset($measurement->latest_measurements);
            if(@$measurement->info_lines) unset($measurement->info_lines);
            if(@$measurement->canvas_data_backup) unset($measurement->canvas_data_backup);
            if(@$measurement->migrated_canvas_data_backup) unset($measurement->migrated_canvas_data_backup);

            // if($measurement->label == 'SBP' || $measurement->label = 'DBP'){
            //     continue;
            // }
            $x[] = $measurement;
        }

        // dd($measurements);

        return $measurements;
    }

    public function getMeasurements($_onlyUnstamped = true)
    {
        $measurementsQuery = Measurement::where('is_removed', false);
        if ($this->pro_type != 'ADMIN') {
            $measurementsQuery
                ->whereIn('client_id', $this->getMyClientIds());
        }
        if ($_onlyUnstamped) {
            $measurementsQuery
                ->whereNotNull('client_bdt_measurement_id')
                ->whereNotNull('ts')
                ->where('is_cellular_zero', false)
                ->where(function ($q) {
                    $q->whereNull('status')
                        ->orWhere(function ($q2) {
                            $q2->where('status', '<>', 'ACK')
                                ->where('status', '<>', 'INVALID_ACK');
                        });
                });
        }
	    $x = [];
        $measurements = $measurementsQuery->orderBy('ts', 'desc')->paginate(50);

        // eager load stuff needed in JS
        foreach ($measurements as $measurement) {
//            if ($measurement->client_bdt_measurement_id) {
//                $measurement->bdtMeasurement = $measurement->clientBDTMeasurement->measurement;
//            }
            unset($measurement->clientBDTMeasurement); // we do not need this travelling to the frontend

            $client = [
                "uid" => $measurement->client->uid,
                "name" => $measurement->client->displayName(),
            ];
            $measurement->patient = $client;

            $measurement->careMonth = $measurement->client->currentCareMonth();
            $measurement->timestamp = friendly_date_time($measurement->created_at);

            unset($measurement->client); // we do not need this travelling to the frontend
            // if($measurement->label == 'SBP' || $measurement->label = 'DBP'){
            //     continue;
	        // }
	        $x[] = $measurement;
	    }

        return $measurements;
    }

    public function companyPros()
    {
        return $this->hasMany(CompanyPro::class, 'pro_id', 'id')
            ->where('is_active', true);
    }

    public function companyProPayers()
    {
        return $this->hasMany(CompanyProPayer::class, 'pro_id', 'id');
    }

    public function isAssociatedWithMCPayer() {
        $companyProPayers = $this->companyProPayers;
        $foundMC = false;
        if($companyProPayers) {
            foreach ($companyProPayers as $companyProPayer) {
                if($companyProPayer->payer && $companyProPayer->payer->is_medicare) {
                    $foundMC = true;
                    break;
                }
            }
        }
        return $foundMC;
    }

    public function isAssociatedWithNonMCPayer($_payerID) {
        $companyProPayers = $this->companyProPayers;
        $foundNonMC = false;
        if($companyProPayers) {
            foreach ($companyProPayers as $companyProPayer) {
                if($companyProPayer->payer && !$companyProPayer->payer->is_medicare && $companyProPayer->payer->id === $_payerID) {
                    $foundNonMC = true;
                    break;
                }
            }
        }
        return $foundNonMC;
    }

    public function companyLocations() {
        $companyProPayers = $this->companyProPayers;
        $companyIDs = [];
        foreach ($companyProPayers as $companyProPayer) {
            $companyIDs[] = $companyProPayer->company_id;
        }
        $locations = [];
        if(count($companyIDs)) {
            $locations = CompanyLocation::whereIn('id', $companyIDs)->get();
        }
        return $locations;
    }

    public function shadowClient() {
        return $this->hasOne(Client::class, 'id', 'shadow_client_id');
    }

    public function defaultCompanyPro() {
        return $this->hasOne(CompanyPro::class, 'id', 'default_company_pro_id');
    }

    public function currentNotePickupForProcessing() {
        return $this->hasOne(NotePickupForProcessing::class, 'id', 'current_note_pickup_for_processing_id');
    }

    public function get_patients_not_seen_in_45_days_count_as_mcp(){
        return 0;
    }

    //DNA_DASHBOARD
    
    //queries
    private function patientsQueryAsDna(){
        // WHERE na_pro_id = :me.id
        return Client::where('default_na_pro_id', $this->id);
    }

    private function patientsAwaitingMcpVisitQueryAsDna(){
        // WHERE has_mcp_done_onboarding_visit <> 'YES'
        return Client::where('default_na_pro_id', $this->id)->where('has_mcp_done_onboarding_visit', '<>', 'YES');
    }

    private function patientsWithoutAppointmentQueryAsDna(){
        // WHERE today_mcp_appointment_date IS NULL AND next_mcp_appointment_date IS NULL
        return Client::where('default_na_pro_id', $this->id)
                        ->whereNull('today_mcp_appointment_date')
                        ->whereNull('next_mcp_appointment_date');
    }
    
    private function encountersPendingMyReviewQueryAsDna(){
        // WHERE ally_pro_id = me.id AND is_cancelled IS NOT TRUE AND is_signed_by_hcp IS TRUE AND is_signed_by_ally IS NOT TRUE;
        return Note::where('ally_pro_id', $this->id)
            ->where('is_cancelled', '<>', true)
            ->where('is_signed_by_hcp', true)
            ->where('is_signed_by_ally','<>', true);
    }

    private function encountersInProgressQueryAsDna(){
        // SELECT * FROM note WHERE ally_pro_id = me.id AND is_cancelled IS NOT TRUE AND is_signed_by_hcp IS NOT TRUE ORDER BY effective_dateest DESC, created_at DESC;
        return Note::where('ally_pro_id', $this->id)
                        ->where('is_cancelled', '<>', true)
                        ->where('is_signed_by_hcp', '<>', true);
    }

    private function appointmentsPendingConfirmationQueryAsDna(){
        // WHERE client_id IN (SELECT id FROM client WHERE default_na_pro_id = :me.id) AND status = 'PENDING'
        $myId = $this->id;
        return Appointment::whereHas('client', function($clientQuery) use ($myId) {
            return $clientQuery->where('default_na_pro_id', $myId);
        })->where('status', 'PENDING');
    }
    
    private function cancelledAppointmentsPendingAckQueryAsDna(){
        // WHERE client_id IN (SELECT id FROM client WHERE default_na_pro_id = :me.id) AND status = 'CANCELLED' AND is_status_acknowledgement_from_default_na_pending IS TRUE;
        $myId = $this->id;
        return Appointment::whereHas('client', function($clientQuery) use ($myId) {
            return $clientQuery->where('default_na_pro_id', $myId);
        })->where('status', 'CANCELLED')
            ->where('is_status_acknowledgement_from_default_na_pending', true);
    }
    
    private function reportsPendingAckQueryAsDna(){
        // WHERE client_id IN (SELECT id FROM client WHERE default_na_pro_id = :me.id) AND has_na_pro_signed IS FALSE AND is_entry_error
        $myId = $this->id;
        return IncomingReport::whereHas('client',function($clientQuery) use ($myId) {
            return $clientQuery->where('default_na_pro_id', $myId);
        })->where('has_na_pro_signed', '<>', true)
            ->where('is_entry_error','<>', true);
    }
    
    private function supplyOrdersPendingMyAckQueryAsDna(){
        // WHERE client_id IN (SELECT id FROM client WHERE default_na_pro_id = :me.id) AND has_na_pro_signed IS FALSE AND is_signed_by_pro IS TRUE AND is_cancelled IS NOT TRUE;
        $myId = $this->id;
        return SupplyOrder::whereHas('client',function($clientQuery) use ($myId) {
            return $clientQuery->where('default_na_pro_id', $myId);
        })->where('has_na_pro_signed', '<>', true)
            ->where('is_signed_by_pro', true)
            ->where('is_cancelled', '<>', true);
    }

    private function supplyOrdersPendingHcpApprovalQueryAsDna(){
        // WHERE client_id IN (SELECT id FROM client WHERE default_na_pro_id = :me.id) AND has_na_pro_signed IS TRUE AND is_signed_by_pro IS NOT TRUE AND is_cancelled IS NOT TRUE;
        $myId = $this->id;
        return SupplyOrder::whereHas('client',function($clientQuery) use ($myId) {
            return $clientQuery->where('default_na_pro_id', $myId);
        })->where('has_na_pro_signed', true)
            ->where('is_signed_by_pro','<>', true)
            ->where('is_cancelled', '<>', true);
    }

    //counts
    public function patientsCountAsDna(){
        return $this->patientsQueryAsDna()->count();
    }
    public function patientsAwaitingMcpVisitCountAsDna(){
        return $this->patientsAwaitingMcpVisitQueryAsDna()->count();
    }
    public function patientsWithoutAppointmentCountAsDna(){
        return $this->patientsWithoutAppointmentQueryAsDna()->count();
    }
    public function encountersPendingMyReviewCountAsDna(){
        return $this->encountersPendingMyReviewQueryAsDna()->count();
    }
    public function encountersInProgressCountAsDna(){
        return $this->encountersInProgressQueryAsDna()->count();
    }
    public function appointmentsPendingConfirmationCountAsDna(){
        return $this->appointmentsPendingConfirmationQueryAsDna()->count();
    }
    public function cancelledAppointmentsPendingAckCountAsDna(){
        return $this->cancelledAppointmentsPendingAckQueryAsDna()->count();
    }
    public function reportsPendingAckCountAsDna(){
        return $this->reportsPendingAckQueryAsDna()->count();
    }
    public function supplyOrdersPendingMyAckCountAsDna(){
        return $this->supplyOrdersPendingMyAckQueryAsDna()->count();
    }
    public function supplyOrdersPendingHcpApprovalCountAsDna(){
        return $this->supplyOrdersPendingHcpApprovalQueryAsDna()->count();
    }

    //records
    private $DNA_RESULTS_PAGE_SIZE = 50;

    public function patientsRecordsAsDna(){
        return $this->patientsQueryAsDna()->paginate($this->DNA_RESULTS_PAGE_SIZE);
    }
    public function patientsAwaitingMcpVisitRecordsAsDna(){
        return $this->patientsAwaitingMcpVisitQueryAsDna()->paginate($this->DNA_RESULTS_PAGE_SIZE);
    }
    public function patientsWithoutAppointmentRecordsAsDna(){
        return $this->patientsWithoutAppointmentQueryAsDna()->paginate($this->DNA_RESULTS_PAGE_SIZE);
    }
    public function encountersPendingMyReviewRecordsAsDna(){
        return $this->encountersPendingMyReviewQueryAsDna()
                ->orderBy('effective_dateest', 'desc')
                ->orderBy('created_at', 'desc')
                ->paginate($this->DNA_RESULTS_PAGE_SIZE);
    }
    public function encountersInProgressRecordsAsDna(){
        return $this->encountersInProgressQueryAsDna()->paginate($this->DNA_RESULTS_PAGE_SIZE);
    }
    public function appointmentsPendingConfirmationRecordsAsDna(){
        return $this->appointmentsPendingConfirmationQueryAsDna()->paginate($this->DNA_RESULTS_PAGE_SIZE);
    }
    public function cancelledAppointmentsPendingAckRecordsAsDna(){
        return $this->cancelledAppointmentsPendingAckQueryAsDna()->paginate($this->DNA_RESULTS_PAGE_SIZE);
    }
    public function reportsPendingAckRecordsAsDna(){
        return $this->reportsPendingAckQueryAsDna()->paginate($this->DNA_RESULTS_PAGE_SIZE);
    }
    public function supplyOrdersPendingMyAckRecordsAsDna(){
        return $this->supplyOrdersPendingMyAckQueryAsDna()->paginate($this->DNA_RESULTS_PAGE_SIZE);
    }
    public function supplyOrdersPendingHcpApprovalRecordsAsDna(){
        return $this->supplyOrdersPendingHcpApprovalQueryAsDna()->paginate($this->DNA_RESULTS_PAGE_SIZE);
    }

    public function measurementsPendingReviewAsDna(){
        //Measurements Pending Review 
        // SELECT * FROM measurement WHERE client_id IN (SELECT id FROM client WHERE rmm_pro_id = :me.id) 
        // AND has_been_stamped_by_rmm IS FALSE AND is_cellular IS TRUE AND is_cellular_zero IS NOT TRUE AND is_active IS TRUE ORDER BY ts DESC;
        $myId = $this->id;
        return Measurement::whereHas('client',function($clientQuery) use ($myId) {
                return $clientQuery->where('rmm_pro_id', $myId);
            })
            ->where('has_been_stamped_by_rmm', '<>', true)
            ->where('is_cellular', true)
            ->where('is_cellular_zero', '<>', true)
            ->where('is_active', true)
            ->orderBy('ts', 'DESC')
            ->paginate(15);
    }

    public function getProcessingAmountAsDna(){
        $expectedForCm = DB::select(DB::raw("SELECT coalesce(SUM(cm_expected_payment_amount),0) as expected_pay  FROM bill WHERE cm_pro_id = :performerProID  AND has_cm_been_paid = false AND is_signed_by_cm IS TRUE AND is_cancelled = false"), ['performerProID' => $this->id])[0]->expected_pay;
        $expectedForRme = DB::select(DB::raw("SELECT coalesce(SUM(rme_expected_payment_amount),0) as expected_pay  FROM bill WHERE rme_pro_id = :performerProID  AND has_rme_been_paid = false AND is_signed_by_rme IS TRUE AND is_cancelled = false"), ['performerProID' => $this->id])[0]->expected_pay;
        $expectedForRmm = DB::select(DB::raw("SELECT coalesce(SUM(rmm_expected_payment_amount),0) as expected_pay  FROM bill WHERE rmm_pro_id = :performerProID  AND has_rmm_been_paid = false AND is_signed_by_rmm IS TRUE AND is_cancelled = false"), ['performerProID' => $this->id])[0]->expected_pay;
        $expectedForNa = DB::select(DB::raw("SELECT coalesce(SUM(generic_pro_expected_payment_amount),0) as expected_pay  FROM bill WHERE generic_pro_id = :performerProID  AND has_generic_pro_been_paid = false AND is_signed_by_generic_pro IS TRUE AND is_cancelled = false"), ['performerProID' => $this->id])[0]->expected_pay;
        $totalExpectedAmount =  $expectedForCm + $expectedForRme + $expectedForRmm + $expectedForNa;

        return $totalExpectedAmount;
    }

    public function getNextPaymentDateAsDna(){
        $nextPaymentDate = '--';
        //if today is < 15th, next payment is 15th, else nextPayment is
        $today = strtotime(date('Y-m-d'));
        $todayDate = date('j', $today);

        $todayMonth =  date('m', $today);
        $todayYear = date('Y', $today);
        if ($todayDate < 15) {
            $nextPaymentDate = new DateTime(); 
            $nextPaymentDate->setDate($todayYear, $todayMonth, 15);
            $nextPaymentDate = $nextPaymentDate->format('m/d/Y');
        } else {
            $nextPaymentDate = new \DateTime();
            $lastDayOfMonth = date('t', $today);
            $nextPaymentDate->setDate($todayYear, $todayMonth, $lastDayOfMonth);
            $nextPaymentDate = $nextPaymentDate->format('m/d/Y');
        }

        return $nextPaymentDate;
    }       


    public function clientSmsesAsDna(){
        $myId = $this->id;
        return ClientSMS::whereHas('client', function($clientQuery) use ($myId){
            return $clientQuery->where('default_na_pro_id', $myId);
        })
        ->orderBy('created_at', 'DESC')
        ->paginate(15);
    }

    public function clientMemosAsDna(){
        $naClientMemos = DB::select(
            DB::raw("
SELECT c.uid as client_uid, c.name_first, c.name_last,
       cm.uid, cm.content, cm.created_at
FROM client c join client_memo cm on c.id = cm.client_id
WHERE
      c.default_na_pro_id = {$this->id}
ORDER BY cm.created_at DESC
            ")
        );

        return $naClientMemos;
    }

    public function getAppointmentsPendingStatusChangeAckAsDna() {
        $myId = $this->id;
        return Appointment::whereHas('client', function($clientQuery) use ($myId){
            return $clientQuery->where('default_na_pro_id', $myId);
        })
            ->where('is_status_acknowledgement_from_appointment_pro_pending', true)
            ->where('raw_date', '>=', DB::raw('NOW()'))
            ->orderBy('raw_date', 'asc')
            ->get();
    }
    public function getAppointmentsPendingDecisionAckAsDna() {
        $myId = $this->id;
        return Appointment::whereHas('client', function($clientQuery) use ($myId){
            return $clientQuery->where('default_na_pro_id', $myId);
        })
            ->where('is_decision_acknowledgement_from_appointment_pro_pending', true)
            ->where('raw_date', '>=', DB::raw('NOW()'))
            ->orderBy('raw_date', 'asc')
            ->get();
    }
    public function getAppointmentsPendingTimeChangeAckAsDna() {
        $myId = $this->id;
        return Appointment::whereHas('client', function($clientQuery) use ($myId){
            return $clientQuery->where('default_na_pro_id', $myId);
        })
            ->where('is_time_change_acknowledgement_from_appointment_pro_pending', true)
            ->where('raw_date', '>=', DB::raw('NOW()'))
            ->orderBy('raw_date', 'asc')
            ->get();
    }

    function myGenericBills() {
        return Bill::where('bill_service_type', 'GENERIC')
            ->where('generic_pro_id', $this->id)
            ->orderBy('created_at', 'DESC')->get();
    }       


}