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()
    {
        return $this->name_last . ', ' . $this->name_first;
    }

    public function mcp()
    {
        return $this->hasOne(Pro::class, 'id', 'mcp_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 prosInMeetingWith()
    {
        return Pro::where('in_meeting_with_client_id', $this->id)->get();
    }

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

    public function prescriptions()
    {
        return $this->hasMany(Erx::class, 'client_id', 'id')
            ->orderBy('created_at', 'desc')
            ->orderByRaw('note_id DESC NULLS LAST');
    }

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

    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)
            ->orderBy('effective_dateest', 'desc');
    }

    public function cancelledNotes()
    {
        return $this->hasMany(Note::class, 'client_id', 'id')
            ->where('is_cancelled', true)
            ->orderBy('effective_dateest', '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_removed', false)
            ->orderByRaw('ts DESC NULLS LAST');
    }

    public function recentMeasurements()
    {
        return $this->hasMany(Measurement::class, 'client_id', 'id')
            ->where('is_removed', false)
            ->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_removed', false)
            ->where('is_cellular_zero', false)
            ->orderBy('effective_date', 'desc');
    }

    public function getNonZeroBpMeasurements(){
        return $this->hasMany(Measurement::class, 'client_id', 'id')
            /*->distinct('label')*/
            ->where('is_removed', false)
            ->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_removed', false)
            ->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 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_removed', false)
            ->orderBy('ts', 'desc')
            ->get();
        return $measurements;
    }

    public function allMeasurements()
    {
        return $this->hasMany(Measurement::class, 'client_id', 'id')
            ->where('is_removed', false)
            ->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 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()
    {
        if ($this->mcp) {
            return Appointment::where('client_id', $this->id)
                ->where('pro_id', $this->mcp->id)
                ->where('start_time', '>=', date('Y-m-d'))
                ->orderBy('start_time', 'asc')
                ->first();
        }
        return false;
    }

    public function lastMcpAppointment()
    {
        if ($this->mcp) {
            return Appointment::where('client_id', $this->id)
                ->where('pro_id', $this->mcp->id)
                ->where('start_time', '<', date('Y-m-d'))
                ->orderBy('start_time', 'asc')
                ->first();
        }
        return false;
    }

    public function lastMeasurementOfType($_type) {
        return Measurement::where('client_id', $this->id)
            ->whereNotNull('bdt_measurement_id')
            ->whereNotNull('ts')
            ->where('is_cellular_zero', false)
            ->where('is_removed', false)
            ->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('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', ['CREATED', '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', ['CONFIRMED', 'CREATED'])
            ->orderBy('start_time')
            ->first();
    }

    public function appointmentsFromLastWeek()
    {

        $dateLastWeek = date_sub(date_create(), date_interval_create_from_date_string("7 days"));
        $dateLastWeek = date_format($dateLastWeek, "Y-m-d");
        return $this->hasMany(Appointment::class, 'client_id', 'id')
            ->where('raw_date', '>=', $dateLastWeek)
            ->whereIn('status', ['CREATED', 'CONFIRMED'])
            ->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" => 'Nurse'];

        // 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" => 'Via Client-Pro Access'];
        }

        // 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')
            ->orderBy('position_index', 'ASC')
            ->orderBy('code', 'ASC');
    }

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

    public function cmSetupNote()
    {
        return $this->hasOne(Note::class, 'id', 'cm_setup_note_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 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 = 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_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_removed IS FALSE
        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)
                ->orderBy('created_at')
                ->get();

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

        return $days;

    }

    public function getPrimaryCoverage()
    {
        // try the latest manual coverage
        $coverage = $this->latestManualClientPrimaryCoverage;
        if (!$coverage) {
            // try the latest coverage
            $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 '-';
    }
}