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($_flat = false) {
        $name = [];
        if(!empty($this->name_display)) return $this->name_display;
        $sep = ", ";
        if($_flat) {
            if(!empty($this->name_first)) $name[] = $this->name_first;
            if(!empty($this->name_last)) $name[] = $this->name_last;
            $sep = ' ';
        }
        else {
            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($sep, $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 teamsWhereMcp()
    {
        return $this->hasMany(ProTeam::class, 'mcp_pro_id', 'id');
    }
    
    public function teamsWhereAssistant()
    {
        return $this->hasMany(ProTeam::class, 'assistant_pro_id', 'id');
    }

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

    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')
            ->where('responsibility', '<>', 'NA')
            ->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() {
        if($this->pro_type == 'ADMIN'){
            return VisitTemplate::where('is_active' , true)->get();
        }

        $allAccessibleVisitTemplatesIDs = VisitTemplate::where('is_accessible_to_all', true)->where('is_active', true)->pluck('id')->toArray();
        $accesses = VisitTemplateAccess::where('pro_id', $this->id)->where('is_active', true)->pluck('visit_template_id')->toArray();

        $allAllowedVisitTemplatesIDs = array_unique(array_merge($allAccessibleVisitTemplatesIDs, $accesses));
        
        return VisitTemplate::whereIn('id', $allAllowedVisitTemplatesIDs)->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_admin() {
        $query = Client::whereNull('shadow_pro_id');
        return $query->where('is_active', true)
            ->where(function ($q) {
                $q->whereNull('client_engagement_status_category')
                    ->orWhere('client_engagement_status_category', '<>', 'DUMMY');
            })->count();
    }

    function get_patients_count_as_mcp() {
        $query = Client::whereNull('shadow_pro_id');
        return $query->where('mcp_pro_id', $this->id)
            ->where(function ($q) {
                $q->whereNull('client_engagement_status_category')
                    ->orWhere('client_engagement_status_category', '<>', 'DUMMY');
            })->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_new_patients_awaiting_visit_count_as_admin() {
        $query = Client::whereNull('shadow_pro_id');
        return $query->where('has_mcp_done_onboarding_visit', '!=', 'YES')
            ->where(function ($q) {
                $q->whereNull('client_engagement_status_category')
                    ->orWhere('client_engagement_status_category', '<>', 'DUMMY');
            })
            ->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_signature_count_as_admin() {
        return Note::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::whereHas('proposedSegmentSummarySuggestion', function($sugQuery){
            return $sugQuery->where('status', '=', 'PENDING');
        })->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_rejected_summary_suggestion_as_mcp_query(){
        $segmentsWithProposedSummarySuggestion = Segment::whereHas('proposedSegmentSummarySuggestion', function($sugQuery){
            return $sugQuery->where('status', '=', 'REJECTED');
        })->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_rejected_summary_suggestion_count_as_mcp(){
        return $this->get_notes_rejected_summary_suggestion_as_mcp_query()->count();
    }

    function get_notes_rejected_summary_suggestion_as_mcp(){
        return $this->get_notes_rejected_summary_suggestion_as_mcp_query()->get();
    }

    function get_notes_pending_summary_suggestion_as_admin_query(){
        $segmentsWithProposedSummarySuggestion = Segment::whereHas('proposedSegmentSummarySuggestion', function($sugQuery){
            return $sugQuery->where('status', '=', 'PENDING');
        })->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_rejected_summary_suggestion_as_admin_query(){
        $segmentsWithProposedSummarySuggestion = Segment::whereHas('proposedSegmentSummarySuggestion', function($sugQuery){
            return $sugQuery->where('status', '=', 'REJECTED');
        })->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_rejected_summary_suggestion_count_as_admin(){
        return $this->get_notes_rejected_summary_suggestion_as_admin_query()->count();
    }

    function get_notes_rejected_summary_suggestion_as_admin(){
        return $this->get_notes_rejected_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_notes_pending_billing_count_as_admin() {
        return Note::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_incoming_reports_pending_signature_count_as_admin() {
        return IncomingReport::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_without_appointment_count_as_admin() {
        return Client::whereNull('shadow_pro_id')
            ->where('is_active', true)
            ->where(function ($q) {
                $q->whereNull('client_engagement_status_category')
                    ->orWhere('client_engagement_status_category', '<>', 'DUMMY');
            })
            ->whereNull('today_mcp_appointment_date')
            ->where(function($q){
                $q->whereNull('next_mcp_appointment_id')
                    ->orWhere('next_mcp_appointment_date', '<', DB::raw('NOW()::DATE'));
            })->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_unsigned_incoming_reports_count_as_mcp() {
        return IncomingReport::where('hcp_pro_id', $this->id)
            ->whereRaw('(has_hcp_pro_signed IS NULL OR has_hcp_pro_signed = FALSE)')
            ->count();
    }

    function get_unsigned_rpm_bills_count_as_mcp() {

        $query = "
SELECT count(distinct(care_month.id))
FROM care_month join client on care_month.client_id = client.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
    left join bill on care_month.mcp_rm_generic_bill_id = bill.id
    left join note mrmnote on mrmnote.id = (
        select max(n.id) from note n 
        where 
              n.client_id = client.id AND 
              n.is_cancelled = FALSE AND
              (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
              n.effective_dateest::date >= care_month.start_date::date AND 
              n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
     )
WHERE
      care_month.mcp_pro_id = {$this->id}
      AND bill.created_at::date >= NOW()::date - interval '60 days'
      AND care_month.mcp_rm_generic_bill_id IS NOT NULL
      AND bill.is_signed_by_generic_pro IS NOT TRUE
";

        // dd($query);

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

        return $count[0]->count;
    }

    function get_unsigned_rpm_bills_count_as_rmm() {

        $query = "
SELECT count(distinct(care_month.id))
FROM care_month join client on care_month.client_id = client.id join note mrnote on client.most_recent_completed_mcp_note_id = mrnote.id
    left join bill on care_month.rmm_rm_generic_bill_id = bill.id
    left join note mrmnote on mrmnote.id = (
        select max(n.id) from note n 
        where 
              n.client_id = client.id AND 
              n.is_cancelled = FALSE AND
              (n.is_signed_by_hcp IS NOT NULL AND n.is_signed_by_hcp = TRUE) AND
              n.effective_dateest::date >= care_month.start_date::date AND 
              n.effective_dateest::date < (care_month.start_date::date + INTERVAL '1 month')
     )
WHERE
      care_month.rmm_pro_id = {$this->id}
      AND EXTRACT(MONTH from care_month.start_date) = EXTRACT(MONTH from now())
      AND EXTRACT(YEAR from care_month.start_date) = EXTRACT(YEAR from now())
      AND care_month.rmm_rm_generic_bill_id IS NOT NULL
      AND bill.is_signed_by_generic_pro = TRUE
";

        // dd($query);

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

        return $count[0]->count;
    }

    function get_patients_awaiting_rpm_interaction_count_as_mcp() {
        $cmStartDate = date('Y-m-01');
        $query = "
SELECT
    count(client.id)
FROM
    client join care_month on client.id = care_month.client_id
WHERE
    care_month.start_date = '{$cmStartDate}'
    AND care_month.is_client_enrolled_in_rm IS TRUE
    AND care_month.has_mcp_interacted_with_client_about_rm IS NOT TRUE
    AND care_month.mcp_pro_id = {$this->id}
    AND (client.client_engagement_status_category IS NULL OR client.client_engagement_status_category != 'DUMMY')
    AND client.name_first NOT ILIKE '%test%'
    AND client.name_last NOT ILIKE '%test%'
        ";
        $count = DB::select($query);
        return $count[0]->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])
                    ->orWhereRaw('id IN (SELECT client_id FROM client_review_request WHERE pro_id = ? AND access_start_date <= NOW() AND access_end_date >= NOW() AND client_review_request.is_active IS true)', [$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_admin() {

    }

    public function get_patients_not_seen_in_45_days_count_as_mcp(){
        return 0;
    }

    public function get_open_tickets_count_as_mcp(){
        $self = $this;
        return Ticket::where(function ($q) use ($self) {
                $q->where('assigned_pro_id', $self->id)
                    ->orWhere('manager_pro_id', $self->id)
                    ->orWhere('ordering_pro_id', $self->id)
                    ->orWhere('initiating_pro_id', $self->id);
            })
            ->where('category', 'other')
            ->where('is_open', true)
            ->count();
    }

    //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();
    }   
    
    public function myNotesPendingSupervisorPhysicianStamping(){
        $pro = $this;
        return Note::where('is_stamped_by_supervising_physician', false)->where('is_flagged_for_supervising_physician_review', true)->whereHas('hcpCompanyPro', function($qry) use($pro){
            return $qry->whereHas('supervisingPhysicianCompanyPro', function($qr) use($pro){
                return $qr->where('pro_id', $pro->id);
            });
        })->count();
    }

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

    public function currentMonthClientReviewRequest($clientID){
        return $this->activeClientReviewRequests->where('client_id', $clientID)->where('scoped_month_start_date', date('Y-m-01'))->first();

    }


    //TODO: move to dashboard controller
    public function followupsScheduledForToday() {
        return DB::select(DB::raw("
            SELECT crfu.id AS crfu_id,
                   crfu.uid,
                crfu.next_follow_up_reason,
                crfu.next_follow_up_memo,
                pro.name_first as pro_first_name, 
                pro.name_last AS pro_last_name,
                c.uid as client_uid,
                (c.name_first || ' ' || c.name_last) as client_name,
                crfu.status AS status
            FROM client_rep_follow_up crfu 
            join client c on crfu.id = c.client_rep_follow_up_id
            join pro pro on pro.id = crfu.client_rep_pro_id
            WHERE crfu.status = 'SCHEDULED' 
            AND crfu.next_follow_up_date = :date
            ORDER BY crfu.created_at DESC;
        "), ['date' => date('Y-m-d')]);
    }

}