stagfe2/app/Models/Client.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Relations\HasOne;
use Illuminate\Support\Collection;

# use Illuminate\Database\Eloquent\Model;

use Illuminate\Support\Facades\DB;

class Client extends Model
{
    protected $table = 'client';

    public function primaryCoverages()
    {
        return $this->hasMany(ClientPrimaryCoverage::class, 'client_id', 'id')
            ->orderBy('created_at', 'desc');
    }

    public function latestClientPrimaryCoverage(){
        return $this->hasOne(ClientPrimaryCoverage::class, 'id', 'latest_client_primary_coverage_id');
    }
	public function latestNewClientPrimaryCoverage(){
        return $this->hasOne(ClientPrimaryCoverage::class, 'id', 'latest_new_client_primary_coverage_id');
    }
	public function latestAutoRefreshClientPrimaryCoverage(){
        return $this->hasOne(ClientPrimaryCoverage::class, 'id', 'latest_auto_refresh_client_primary_coverage_id');
    }
	public function latestManualClientPrimaryCoverage(){
        return $this->hasOne(ClientPrimaryCoverage::class, 'id', 'latest_manual_client_primary_coverage_id');
    }
	public function temporaryOutsiderNewClientPrimaryCoverage(){
        return $this->hasOne(ClientPrimaryCoverage::class, 'id', 'temporary_outsider_new_client_primary_coverage_id');
    }

    public function displayName($_flat = true)
    {
        $result = '';
        if($_flat) {
            $result = $this->name_first . ' ' . $this->name_last;
        }
        else {
            $result = $this->name_last . ', ' . $this->name_first;
        }
        if($this->client_engagement_status_category == 'DUMMY') {
            $result .= ' [Test Record]';
        }
        if($this->client_engagement_status_category == 'DUPLICATE') {
            $result .= ' [Duplicate Record]';
        }
        return $result;
    }

    public function mcp()
    {
        return $this->hasOne(Pro::class, 'id', 'mcp_pro_id');
    }

    public function rd()
    {
        return $this->hasOne(Pro::class, 'id', 'rd_pro_id');
    }

    public function pcp()
    {
        return $this->hasOne(Pro::class, 'id', 'physician_pro_id');
    }

    public function cm()
    {
        return $this->hasOne(Pro::class, 'id', 'cm_pro_id');
    }

    public function rmm()
    {
        return $this->hasOne(Pro::class, 'id', 'rmm_pro_id');
    }

    public function rme()
    {
        return $this->hasOne(Pro::class, 'id', 'rme_pro_id');
    }

    public function rms()
    {
        return $this->hasOne(Pro::class, 'id', 'rms_pro_id');
    }

    public function rmg()
    {
        return $this->hasOne(Pro::class, 'id', 'rmg_pro_id');
    }

    public function defaultNaPro()
    {
        return $this->hasOne(Pro::class, 'id', 'default_na_pro_id');
    }

    public function creator()
    {
        return $this->hasOne(Pro::class, 'id', 'created_by_pro_id');
    }

    public function prosInMeetingWith()
    {
        return Pro::where('in_meeting_with_client_id', $this->id)->get();
    }

    public function notes()
    {
        return $this->hasMany(Note::class, 'client_id', 'id')
           // ->where('is_core_note', false)
            ->orderBy('effective_dateest', 'desc')
            ->orderBy('created_at', 'desc');
    }

    public function notesPendingClaimsClosed()
    {
        return $this->hasMany(Note::class, 'client_id', 'id')
            ->where('is_claim_closed', false)
            ->where('is_signed_by_hcp', true)
            ->where('is_cancelled', false)
            ->orderBy('effective_dateest', 'desc')
            ->orderBy('created_at', 'desc');
    }

    public function prescriptions()
    {
        return $this->hasMany(Erx::class, 'client_id', 'id')
            /*->where(function ($q) {
                $q->whereNull('pro_declared_status')
                    ->orWhere('pro_declared_status', '<>', 'CANCELLED');
            })*/
            ->orderBy('created_at', 'desc')
            ->orderByRaw('note_id DESC NULLS LAST');
    }

    public function prescriptionsCreatedInNote($note)
    {
        return Erx::where('client_id', $this->id)
            ->where('note_id', $note->id)
            ->orderBy('created_at', 'desc')
            ->where(function ($q) {
                $q->whereNull('pro_declared_status')
                    ->orWhere('pro_declared_status', '<>', 'CANCELLED');
            })
            ->get();
    }

    public function notesAscending()
    {
        return $this->hasMany(Note::class, 'client_id', 'id')
            ->orderBy('effective_dateest', 'asc')
            ->orderBy('created_at', 'desc');;
    }

    public function mcCodeChecks(){

	// $tables = DB::select("SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema'");

	// foreach ($tables as $table) {
	// dump($table);
// }

// die();
	return $this->hasMany(McCodeCheck::class, 'client_id', 'id')->orderBy('created_at', 'asc');
    }

    public function activeNotes()
    {
        return $this->hasMany(Note::class, 'client_id', 'id')
            ->where('is_cancelled', false)
            ->where('id', '<>', $this->core_note_id)
            ->orderBy('effective_dateest', 'desc')
            ->orderBy('created_at', 'desc');;
    }

    public function cancelledNotes()
    {
        return $this->hasMany(Note::class, 'client_id', 'id')
            ->where('is_cancelled', true)
            ->where('id', '<>', $this->core_note_id)
            ->orderBy('effective_dateest', 'desc')
            ->orderBy('created_at', 'desc');;
    }

    public function sections()
    {
        return $this->hasMany(Section::class, 'client_id', 'id')
            ->where('is_active', true)
            ->orderBy('created_at', 'asc');
    }

    public function handouts()
    {
        $mappings = HandoutClient::where('client_id', $this->id)->get();
        $handouts = new Collection();
        foreach ($mappings as $mapping) {
            $handout = Handout::where('id', $mapping->handout_id)->first();
            $handout->handout_client_uid = $mapping->uid;
            $handouts->add($handout);
        }
        $handouts = $handouts->sortBy('created_at');
        return $handouts;
    }

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

    public function actionItems()
    {
        return $this->hasMany(ActionItem::class, 'client_id', 'id')
            ->orderBy('action_item_category', 'asc')
            ->orderBy('created_at', 'desc');
    }

    public function infoLines()
    {
        return $this->hasMany(ClientInfoLine::class, 'client_id', 'id')->orderBy('created_at', 'desc');
    }

    public function measurements()
    {
        return $this->hasMany(Measurement::class, 'client_id', 'id')
            /*->distinct('label')*/
            ->where('is_active', true)
            ->orderByRaw('ts DESC NULLS LAST');
    }

    public function cellularMeasurements()
    {
        return $this->hasMany(Measurement::class, 'client_id', 'id')
            /*->distinct('label')*/
            ->where('is_active', true)
            ->where('is_cellular', true)
            ->where('is_cellular_zero', false)
            ->orderByRaw('ts DESC NULLS LAST');
    }

    public function recentMeasurements()
    {
        return $this->hasMany(Measurement::class, 'client_id', 'id')
            ->where('is_active', true)
            ->whereNotNull('label')
            ->where('label', '<>', 'SBP')
            ->where('label', '<>', 'DBP')
            ->where('is_cellular_zero', false)
            ->orderByRaw('ts DESC NULLS LAST')
            ->offset(0)->limit(20);
    }

    public function nonZeroMeasurements()
    {
        return $this->hasMany(Measurement::class, 'client_id', 'id')
            /*->distinct('label')*/
            ->where('is_active', true)
            ->where('is_cellular_zero', false)
            ->orderBy('effective_date', 'desc');
    }

    public function getNonZeroBpMeasurements(){
        return $this->hasMany(Measurement::class, 'client_id', 'id')
            /*->distinct('label')*/
            ->where('is_active', true)
            ->where('label', '=', 'BP')
            ->where('sbp_mm_hg', '>', 0)
            ->where('dbp_mm_hg', '>', 0)
            ->orderBy('ts', 'desc');
    }

    public function getNonZeroWeightMeasurements(){
        return $this->hasMany(Measurement::class, 'client_id', 'id')
            /*->distinct('label')*/
            ->where('is_active', true)
            ->where('label', '=', 'Wt. (lbs.)')
            ->where('numeric_value', '>', 0)
            ->orderBy('ts', 'desc');
    }

    public function currentCareMonth()
    {
        $cmStartDate = strtotime(date('Y-m-d'));
        $month = date("n", $cmStartDate);
        $year = date("Y", $cmStartDate);
        return CareMonth
            ::where('client_id', $this->id)
            ->whereRaw('EXTRACT(MONTH FROM start_date) = ?', [$month])
            ->whereRaw('EXTRACT(YEAR FROM start_date) = ?', [$year])
            ->first();
    }

    public function previousCareMonth()
    {
        $cmStartDate = date('Y-m-d', strtotime('first day of last month'));
        
        return CareMonth
            ::where('client_id', $this->id)
            ->where('start_date',  $cmStartDate)
            ->first();
    }

    public function measurementsInCareMonth(CareMonth $careMonth)
    {
        $cmStartDate = strtotime($careMonth->start_date);
        $month = date("n", $cmStartDate);
        $year = date("Y", $cmStartDate);
        $measurements = Measurement
            ::where('client_id', $this->id)
            ->whereRaw('EXTRACT(MONTH FROM effective_date) = ?', [$month])
            ->whereRaw('EXTRACT(YEAR FROM effective_date) = ?', [$year])
            ->where('is_active', true)
            ->orderBy('ts', 'desc')
            ->get();
        return $measurements;
    }

    public function allMeasurements()
    {
        return $this->hasMany(Measurement::class, 'client_id', 'id')
            ->where('is_active', true)
            ->whereNull('parent_measurement_id')
            ->orderBy('label', 'asc')
            ->orderBy('effective_date', 'desc');
    }

    public function smses()
    {
        return $this->hasMany(ClientSMS::class, 'client_id', 'id')
            ->orderBy('created_at', 'desc');
    }

    public function ismses()
    {
        return $this->hasMany(Isms::class, 'client_id', 'id')
            ->orderBy('created_at', 'desc');
    }

    public function documents()
    {
        return $this->hasMany(ClientDocument::class, 'client_id', 'id')
            ->orderBy('created_at', 'desc');
    }

    public function incomingReports() {
        return $this->hasMany(IncomingReport::class, 'client_id', 'id')
            ->orderBy('created_at', 'desc');
    }

    public function smsNumbers() {
        return $this->hasMany(ClientSMSNumber::class, 'client_id', 'id')
            ->orderBy('created_at', 'desc');
    }

    public function nextMcpAppointment()
    {
        return $this->hasOne(Appointment::class, 'id', 'next_mcp_appointment_id');
    }

    public function lastMcpAppointment()
    {
        return $this->hasOne(Appointment::class, 'id', 'previous_mcp_appointment_id');
    }

    public function lastMeasurementOfType($_type) {
        return Measurement::where('client_id', $this->id)
            ->whereNotNull('bdt_measurement_id')
            ->whereNotNull('ts')
            ->where('is_cellular_zero', false)
            ->where('is_active', true)
            ->where('label', '=', $_type)
            ->orderBy('ts', 'desc')
            ->first();
    }

    public function appointments()
    {
        return $this->hasMany(Appointment::class, 'client_id', 'id')
            ->orderBy('start_time', 'desc');
    }

    public function appointmentsForProByStatus($forPro = 'all', $status = 'all')
    {
        $appointments = Appointment::where('client_id', $this->id);
        if($forPro !== 'all') {
            $forPro = Pro::where('uid', $forPro)->first();
            $appointments = $appointments->where('pro_id', $forPro->id);
        }
        if($status !== 'ALL') {
            $appointments = $appointments->where('status', $status);
        }
        $appointments = $appointments->orderBy('raw_date', 'desc')->orderBy('raw_start_time', 'desc');
        return $appointments->get();
    }

    public function upcomingAppointments()
    {
        return $this->hasMany(Appointment::class, 'client_id', 'id')
            ->where('raw_date', '>=', date('Y-m-d'))
            ->whereIn('status', ['PENDING', 'CONFIRMED'])
            ->orderBy('start_time', 'desc')
            ->limit(5);
    }

    public function nextAppointment() {
        return Appointment
            ::where('client_id', $this->id)
            ->where('raw_date', '>=', DB::raw('NOW()'))
            ->whereIn('status', ['PENDING', 'CONFIRMED'])
            ->orderBy('start_time')
            ->first();
    }

    public function appointmentsFromLastWeek()
    {

        $dateLastWeek = date_sub(date_create(), date_interval_create_from_date_string("14 days"));
        $dateLastWeek = date_format($dateLastWeek, "Y-m-d");
        return $this->hasMany(Appointment::class, 'client_id', 'id')
            ->where('raw_date', '>=', $dateLastWeek)
            ->orderBy('start_time', 'desc');
    }

    public function memos()
    {
        return $this->hasMany(ClientMemo::class, 'client_id', 'id')
            ->where('is_cancelled', false)
            ->orderBy('created_at', 'desc');
    }

    public function devices()
    {
        return $this->hasMany(ClientBDTDevice::class, 'client_id', 'id')
            ->where('is_active', true)
            ->orderBy('created_at', 'desc');
    }

    public function deactivatedDevices()
    {
        return $this->hasMany(ClientBDTDevice::class, 'client_id', 'id')
            ->where('is_active', false)
            ->orderBy('created_at', 'desc');
    }

    public function hasDevice($_device)
    {
        $count = ClientBDTDevice::where('client_id', $this->id)
            ->where('device_id', $_device->id)
            ->where('is_active', true)
            ->count();
        return !!$count;
    }

    public function deviceMeasurements()
    {
        return $this->hasMany(ClientBDTMeasurement::class, 'client_id', 'id')
            ->orderBy('created_at', 'desc');
    }

    public function activeMcpRequest()
    {
        return $this->hasOne(McpRequest::class, 'id', 'active_mcp_request_id');
    }

    public function clientPrograms()
    {
        return $this->hasMany(ClientProgram::class, 'client_id', 'id')
            ->where('is_active', true)
            ->orderBy('title', 'desc');
    }

    public function tickets()
    {
        return $this->hasMany(Ticket::class, 'client_id', 'id')
            ->orderBy('is_open', 'desc')
            ->orderBy('created_at', 'desc');
    }

    public function mcpDisplayName()
    {
    }

    public function rmeDisplayName()
    {
    }

    public function supplyOrderForCellularBPDevice() {
        return SupplyOrder::where('product_id', 1)
            ->where('is_cancelled', false)
            ->where('client_id', $this->id)
            ->orderBy('id', 'desc')
            ->first();
    }

    public function supplyOrderForCellularWeightScale() {
        return SupplyOrder::where('product_id', 2)
            ->where('is_cancelled', false)
            ->where('client_id', $this->id)
            ->orderBy('id', 'desc')
            ->first();
    }

    public function firstCellularBPDevice()
    {
        $devices = $this->devices;
        $x = null;
        foreach($devices as $device){
            if($device->device->category == 'BP'){
                $x = $device;
                break;
            }
        }
        return $x;
    }

    public function getFirstCellularBPMeasurementAt()
    {
    }

    public function getLatestCellularBPMeasurementAt()
    {
    }

    public function getTotalCellularBPMeasurements()
    {
    }

    public function firstCellularWeightDevice()
    {
        $devices = $this->devices;
        $x = null;
        foreach($devices as $device){
            if($device->device->category == 'WEIGHT'){
                $x = $device;
                break;
            }
        }
        return $x;
    }

    public function getFirstCellularWeightMeasurementAt()
    {
    }

    public function getLatestCellularWeightMeasurementAt()
    {
    }

    public function getTotalCellularWeightMeasurements()
    {
    }

    public function prosWithAccess()
    {

        $pros = [];

        // directly associated pros
        $pro = $this->mcp;
        if ($pro && $pro->id) $pros[] = ["pro" => $pro->displayName(), "association" => 'MCP'];
        $pro = $this->pcp;
        if ($pro && $pro->id) $pros[] = ["pro" => $pro->displayName(), "association" => 'PCP (Physician)'];
        $pro = $this->cm;
        if ($pro && $pro->id) $pros[] = ["pro" => $pro->displayName(), "association" => 'CM'];
        $pro = $this->rmm;
        if ($pro && $pro->id) $pros[] = ["pro" => $pro->displayName(), "association" => 'RMM'];
        $pro = $this->rme;
        if ($pro && $pro->id) $pros[] = ["pro" => $pro->displayName(), "association" => 'RME'];
        $pro = $this->defaultNaPro;
        if ($pro && $pro->id) $pros[] = ["pro" => $pro->displayName(), "association" => 'Care Coordinator'];

        // via client pro access
        $cpAccesses = ClientProAccess::where('client_id', $this->id)->where('is_active', true)->get();
        foreach ($cpAccesses as $cpAccess) {
            if (!$cpAccess->pro) continue;
            $pros[] = ["pro" => $cpAccess->pro->displayName(), "association" => $cpAccess->reason_category. ' - Via Client Pro Access', 'isClientProAccess'=>true, 'clientProAccess'=>$cpAccess];
        }

        // via appointments
        $appointments = Appointment::where('client_id', $this->id)->get();
        foreach ($appointments as $appointment) {
            if (!$appointment->pro) continue;
            $pros[] = ["pro" => $appointment->pro->displayName(), "association" => 'Via Appointment: ' . $appointment->raw_date];
        }

        // via client program
        $clientPrograms = ClientProgram::where('client_id', $this->id)->where('is_active', true)->get();
        foreach ($clientPrograms as $clientProgram) {
            if ($clientProgram->mcp)
                $pros[] = ["pro" => $clientProgram->mcp->displayName(), "association" => 'Program MCP: ' . $clientProgram->title];
            if ($clientProgram->manager)
                $pros[] = ["pro" => $clientProgram->manager->displayName(), "association" => 'Program Manager: ' . $clientProgram->title];
        }

        // sort by pro name
        $name = array_column($pros, 'pro');
        array_multisort($name, SORT_ASC, $pros);

        return $pros;

    }

    public function mcpRequests()
    {
        return $this->hasMany(McpRequest::class, 'for_client_id', 'id')
            ->orderBy('created_at', 'desc');
    }

    public function eligibleRefreshes()
    {
        return $this->hasMany(ClientEligibleRefresh::class, 'client_id', 'id')
            ->orderBy('created_at', 'desc');
    }

    public function mbPayerValidationResults()
    {
        return $this->hasMany(ClientMBPayerValidationResult::class, 'client_id', 'id')
            ->orderBy('created_at', 'desc');
    }

    public function payer()
    {
        return $this->hasOne(MBPayer::class, 'id', 'mb_payer_id');
    }

    public function supplyOrders()
    {
        return $this->hasMany(SupplyOrder::class, 'client_id', 'id')
            ->orderBy('created_at', 'desc');
    }

    public function activeSupplyOrders()
    {
        return $this->hasMany(SupplyOrder::class, 'client_id', 'id')
            ->where('is_cancelled', false)
            ->orderBy('created_at', 'desc');
    }

    public function cancelledSupplyOrders()
    {
        return $this->hasMany(SupplyOrder::class, 'client_id', 'id')
            ->where('is_cancelled', true)
            ->orderBy('created_at', 'desc');
    }

    public function readyToShipSupplyOrders()
    {
        return $this->hasMany(SupplyOrder::class, 'client_id', 'id')
            ->where('is_cancelled', false)
            ->where('is_cleared_for_shipment', true)
            ->whereNull('shipment_id')
            ->orderBy('created_at', 'desc');
    }

    public function shipments()
    {
        return $this->hasMany(Shipment::class, 'client_id', 'id')
            ->where('is_cancelled', false)
            ->orderBy('created_at', 'desc');
    }

    public function numSignedNotes() {
        return Note::where('client_id', $this->id)
            ->where('is_cancelled', false)
            ->where('is_signed_by_hcp', true)
            ->count();
    }

    public function smsReminders()
    {
        return $this->hasMany(SimpleSMSReminder::class, 'client_id', 'id')
            ->orderBy('created_at', 'asc');
    }

    public function measurementConfirmationNumbers()
    {
        return $this->hasMany(MeasurementConfirmationNumber::class, 'client_id', 'id')
            ->orderBy('created_at', 'asc');
    }

    public function shadowOfPro() {
        return $this->hasOne(Pro::class, 'id', 'shadow_pro_id');
    }

    public function clientTags()
    {
        return $this->hasMany(ClientTag::class, 'client_id', 'id')
            ->where('is_cancelled', false)
            ->orderBy('tag', 'asc');
    }

    public function medicalTeam()
    {
        return $this->hasMany(ClientProAccess::class, 'client_id', 'id')
            ->where('is_active', true)
            ->whereNotNull('pro_id')
            ->orderBy('created_at', 'asc');
    }

    public function accountInvites()
    {
        return $this->hasMany(AccountInvite::class, 'for_client_id', 'id')
            ->orderBy('created_at', 'desc');
    }
    public function linkedAccounts()
    {
        return $this->hasMany(AccountClient::class, 'client_id', 'id')
            ->orderBy('created_at', 'desc');
    }

    public function pages($_type, $_returnShadows, $_note) {
        return Page
            ::where('client_id', $this->id)
            ->where('note_id', $_note->id)
            ->where('category', $_type)
            ->where('is_shadow', !!$_returnShadows)
            ->orderBy('key', 'ASC')
            ->get();
    }

    public function firstPageByCategoryAndKey($_category, $_key) {
        return Page
            ::where('client_id', $this->id)
            ->where('category', $_category)
            ->where('key', $_key)
            ->first();
    }

    public function cmReasons()
    {
        return $this->hasMany(ClientCmRmReason::class, 'client_id', 'id')
            ->where('cm_or_rm', 'CM')
            ->where('is_removed', false)
            ->orderBy('position_index', 'ASC')
            ->orderBy('code', 'ASC');
    }

    public function rmReasons()
    {
        return $this->hasMany(ClientCmRmReason::class, 'client_id', 'id')
            ->where('cm_or_rm', 'RM')
            ->where('is_removed', false)
            ->orderBy('position_index', 'ASC')
            ->orderBy('code', 'ASC');
    }

    public function cmSetupNote()
    {
        return $this->hasOne(Note::class, 'id', 'cm_setup_note_id');
    }

    public function rmSetupCareMonth()
    {
        return $this->hasOne(CareMonth::class, 'id', 'rm_setup_care_month_id');
    }

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

    public function defaultMcpCompanyProPayer()
    {
        return $this->hasOne(CompanyProPayer::class, 'id', 'default_mcp_company_pro_payer_id');
    }

    public function defaultMcpCompanyLocation()
    {
        return $this->hasOne(CompanyLocation::class, 'id', 'default_mcp_company_location_id');
    }

    public function hasNewNoteForPro($_pro) {
        $count = Note::where('client_id', $this->id)->where('hcp_pro_id', $_pro->id)->where('is_cancelled', false)->where('new_or_fu_or_na', 'NEW')->count();
        return !!$count;
    }

    public function systemSourcePro()
    {
        return $this->hasOne(Pro::class, 'id', 'system_source_pro_id');
    }

    public function systemSourceProTeam()
    {
        return $this->hasOne(ProTeam::class, 'id', 'system_source_pro_team_id');
    }

    public function adminEngagementAssessmentStatus(){
        return $this->hasOne(Status::class, 'id', 'admin_engagement_assessment_status_id');
    }

    public function mcpEngagementAssessmentStatus(){
        return $this->hasOne(Status::class, 'id', 'mcp_engagement_assessment_status_id');
    }

    public function defaultNaEngagementAssessmentStatus(){
        return $this->hasOne(Status::class, 'id', 'default_na_engagement_assessment_status_id');
    }

    public function clientSelfSatisfactionStatus(){
        return $this->hasOne(Status::class, 'id', 'client_self_satisfaction_status_id');
    }


    public function recentNotes($_pro = null) {
        $notes = Note::where('client_id', $this->id)->where('is_cancelled', false);
        if($_pro) {
            $notes = $notes->where('hcp_pro_id', $_pro->id);
        }
        $notes = $notes->orderBy('effective_dateest', 'DESC')->limit(5)->get();
        return $notes;
    }

    public function cmMeasurementsMatrix($_careMonth, $pro = null) {

        $days = [];

        $matches = DB::select(
            "
SELECT m.id   AS measurement_id,
       m.uid  AS measurement_uid,
       cm.id  AS care_month_id,
       cm.uid AS care_month_uid,
       m.label,
       m.value,
       m.dbp_mm_hg,
       m.sbp_mm_hg,
       m.value_pulse,
       m.value_irregular,
       m.numeric_value,
       m.effective_date,
       m.ts,
       m.has_been_stamped_by_mcp,
       m.has_been_stamped_by_rmm,
       m.has_been_stamped_by_non_hcp
FROM measurement m
         JOIN care_month cm ON m.care_month_id = cm.id
WHERE m.care_month_id = :careMonthID
        AND m.label NOT IN ('SBP', 'DBP')
        AND m.bdt_measurement_id IS NOT NULL
        AND m.is_active IS TRUE
        AND (m.is_cellular_zero = FALSE or m.is_cellular_zero IS NULL)
        AND m.ts IS NOT NULL
        AND m.client_bdt_measurement_id IS NOT NULL
ORDER BY m.ts DESC
            ",
            ['careMonthID' => $_careMonth->id]
        );

        foreach ($matches as $match) {
            $time = (floor($match->ts / 1000));
            $realTimezone = resolve_timezone('EASTERN');
            $date = new \DateTime("@$time");
            $date->setTimezone(new \DateTimeZone($realTimezone));
            $match->date = $date->format("m/d/Y");
            $match->dateYMD = $date->format("Y-m-d");
            $match->time = $date->format("h:i A");

            // get existing entries for listing
            $match->entries = CareMonthEntry::where('care_month_id', $match->care_month_id)
                ->where('is_removed', false)
                ->where('effective_date', $match->dateYMD);

            if(!!$pro) {
                $match->entries = $match->entries->where('pro_id', $pro->id);
            }

            $match->entries = $match->entries->orderBy('created_at')->get();

            if(!isset($days[$match->date])) {
                $days[$match->date] = [];
            }
            $days[$match->date][] = $match;
        }

        return $days;

    }

    public function getPrimaryCoverage()
    {
        $coverage = $this->latestClientPrimaryCoverage;
        return $coverage;
    }

    // return value will be YES, NO or UNKNOWN
    public function getPrimaryCoverageStatus() {
        $coverage = $this->getPrimaryCoverage();
        if(!$coverage) return 'NO';
        return $coverage->getStatus();
    }

    public function getMcpAssignedOn() {
        $change = ClientProChange::where('client_id', $this->id)
            ->where('new_pro_id', $this->mcp_pro_id)
            ->where('responsibility_type', 'MCP')
            ->orderBy('created_at', 'DESC')
            ->first();
        if(!!$change) {
            return friendlier_date($change->created_at);
        }
        return '-';
    }

    public function coreNote(){
        return $this->hasOne(Note::class, 'id', 'core_note_id');
    }

    public function mostRecentCompletedMcpNote(){
        return $this->hasOne(Note::class, 'id', 'most_recent_completed_mcp_note_id');
    }

    
    public function nonCoreVisitNotes() {
        return $this->hasMany(Note::class, 'client_id', 'id')
            ->where('id', '<>', $this->core_note_id)
            ->whereNotNull('visit_template_id')
            ->orderBy('created_at', 'desc');
    }

    public function clientProChanges() {
        return $this->hasMany(ClientProChange::class, 'client_id', 'id')
            ->orderBy('created_at', 'desc');
    }

    public function mostRecentWeightMeasurement(){        
        return $this->hasOne(Measurement::class, 'id', 'most_recent_weight_measurement_id');
    }

    public function hasBPDevice() {
        $cbds = ClientBDTDevice::where('client_id', $this->id)->get();
        foreach ($cbds as $cbd) {
            if($cbd->is_active && !!$cbd->device && $cbd->device->is_active && $cbd->device->category === 'BP') return true;
        }
        return false;
    }
    public function hasWeightScaleDevice() {
        $cbds = ClientBDTDevice::where('client_id', $this->id)->get();
        foreach ($cbds as $cbd) {
            if($cbd->is_active && !!$cbd->device && $cbd->device->is_active && $cbd->device->category === 'WEIGHT') return true;
        }
        return false;
    }

    public function clientEngagementStatus(){
        return $this->hasOne(Status::class, 'id', 'client_engagement_status_id');
    }

    public function getDeviceDeliveryStatus($productId){
        $result =  DB::select("SELECT sh.status FROM shipment sh LEFT JOIN supply_order so ON so.shipment_id = sh.id WHERE so.product_id  = ".$productId." AND so.client_id = ".$this->id." ORDER BY sh.created_at DESC LIMIT 1");
        if (count($result)){
            return $result[0]->status; 
        }
        return '';
    }
    public function hasDataInCanvas($_type) {
        $page = Page::where('client_id', $this->id)->where('category', 'CANVAS')->where('key', $_type)->first();
        $contentData = [];
        if($page){
            $contentData = json_decode($page->data, true);
        }else{
            if($this->canvas_data) {
                $canvasData = json_decode($this->canvas_data, true);
                if(isset($canvasData[$_type])) {
                    $contentData = $canvasData[$_type];
                    if($_type !== 'vitals') {
                        if(!isset($contentData['items'])){
                            $contentData['items'] = [];
                        }
                    }
                }
            }
        }
        if($_type !== 'vitals') {
            if (isset($contentData['items']) && count($contentData['items'])) {
                return true;
            }
        }
        else {
            foreach ($contentData as $cd) {
                if(isset($cd['value']) && !empty($cd['value'])) return true;
            }
        }
        return false;
    }

    // 4 Infra-red Temperature gun 
    public function temparatureGunDeliveryStatus(){
       return $this->getDeviceDeliveryStatus(4);
    }

    // 3 Pulse Oximeter (
    public function pulseOximeterDeliveryStatus(){
        return $this->getDeviceDeliveryStatus(3);
    }
    // 1 Cellular BP - Standard Arm Cuff (if delivered, then it should show its status as Delivered, Dispatched, In-transit, Returned)
    public function cellularBPDeliveryStatus(){
        return $this->getDeviceDeliveryStatus(1);
    }
    // 2 Weight scale 
    public function weightScaleDeliveryStatus(){
        return $this->getDeviceDeliveryStatus(2);
    }


}