stagfe2/app/Http/Controllers/HomeController.php

<?php

namespace App\Http\Controllers;

use App\Lib\Backend;
use App\Models\Appointment;
use App\Models\AppSession;
use App\Models\ClientMemo;
use App\Models\ClientProChange;
use App\Models\ClientSMS;
use App\Models\Facility;
use App\Models\IncomingReport;
use App\Models\MBPayer;
use App\Models\ProProAccess;
use App\Models\SupplyOrder;
use App\Models\Ticket;
use DateTime;

use App\Models\Client;
use App\Models\Bill;
use App\Models\Measurement;
use App\Models\Note;
use App\Models\Pro;
use App\Models\ProTransaction;
use GuzzleHttp\Cookie\CookieJar;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Cookie;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Http;

class HomeController extends Controller
{

    public function confirmSmsAuthToken(Request $request)
    {
        return view('app/confirm_sms_auth_token');
    }

    public function setPassword(Request $request)
    {
        return view('app/set_password');
    }

    public function setSecurityQuestions(Request $request)
    {
        return view('app/set_security_questions');
    }

    public function postConfirmSmsAuthToken(Request $request)
    {

        try {

            $url = config('stag.backendUrl') . '/session/confirmSmsAuthToken';

            $data = [
                'cellNumber' => $request->input('cellNumber'),
                'token' => $request->input('token'),
            ];

            $response = Http::asForm()
                ->withHeaders(['sessionKey' => $request->cookie('sessionKey')])
                ->post($url, $data)
                ->json();

            if (!isset($response['success']) || !$response['success']) {
                $message = 'API error';
                if (isset($response['error'])) {
                    $message = $response['error'];
                    if (isset($response['path'])) $message .= ': ' . $response['path'];
                } else if (isset($response['message'])) $message = $response['message'];
                return redirect('/confirm_sms_auth_token')
                    ->withInput()
                    ->with('message', $message);
            }

            return redirect('/');
        } catch (\Exception $e) {
            return redirect()->back()
                ->with('message', 'Unable to process your request at the moment. Please try again later.')
                ->withInput($request->input());
        }
    }

    public function resendSmsAuthToken(Request $request)
    {

        try {

            $url =  config('stag.backendUrl') . '/session/resendSmsAuthToken';

            $data = [];

            $response = Http::asForm()
                ->withHeaders(['sessionKey' => $request->cookie('sessionKey')])
                ->post($url, $data)
                ->json();

            if (!isset($response['success']) || !$response['success']) {
                $message = 'API error';
                if (isset($response['error'])) {
                    $message = $response['error'];
                    if (isset($response['path'])) $message .= ': ' . $response['path'];
                } else if (isset($response['message'])) $message = $response['message'];
                return redirect('/confirm_sms_auth_token')
                    ->withInput()
                    ->with('message', $message);
            }

            return redirect()->back()->withInput()->with('message', "SMS Auth Token sent.");
        } catch (\Exception $e) {
            return redirect()->back()
                ->with('message', 'Unable to process your request at the moment. Please try again later.')
                ->withInput($request->input());
        }
    }

    public function postSetPassword(Request $request)
    {
        try {

            $url =  config('stag.backendUrl') . '/pro/selfPutPassword';

            $data = [
                'newPassword' => $request->input('newPassword'),
                'newPasswordConfirmation' => $request->input('newPasswordConfirmation'),
            ];

            $response = Http::asForm()
                ->withHeaders(['sessionKey' => $request->cookie('sessionKey')])
                ->post($url, $data)
                ->json();

            if (!isset($response['success']) || !$response['success']) {
                $message = 'API error';
                if (isset($response['error'])) {
                    $message = $response['error'];
                    if (isset($response['path'])) $message .= ': ' . $response['path'];
                } else if (isset($response['message'])) $message = $response['message'];
                return redirect('/set_password')
                    ->withInput()
                    ->with('message', $message);
            }

            return redirect('/');
        } catch (\Exception $e) {
            return redirect()->back()
                ->with('message', 'Unable to process your request at the moment. Please try again later.')
                ->withInput($request->input());
        }
    }
    public function postSetSecurityQuestions(Request $request)
    {

        try {

            $url = env('BACKEND_URL', 'http://localhost:8080/api') . '/pro/selfPutSecurityQuestions';

            $data = [
                'securityQuestion1' => $request->input('securityQuestion1'),
                'securityAnswer1' => $request->input('securityAnswer1'),
                'securityQuestion2' => $request->input('securityQuestion2'),
                'securityAnswer2' => $request->input('securityAnswer2'),
            ];

            $response = Http::asForm()
                ->withHeaders(['sessionKey' => $request->cookie('sessionKey')])
                ->post($url, $data)
                ->json();

            if (!isset($response['success']) || !$response['success']) {
                $message = 'API error';
                if (isset($response['error'])) {
                    $message = $response['error'];
                    if (isset($response['path'])) $message .= ': ' . $response['path'];
                } else if (isset($response['message'])) $message = $response['message'];
                return redirect('/set_password')
                    ->withInput()
                    ->with('message', $message);
            }

            return redirect('/');
        } catch (\Exception $e) {
            return redirect()->back()
                ->with('message', 'Unable to process your request at the moment. Please try again later.')
                ->withInput($request->input());
        }
    }

    public function dashboard(Request $request)
    {

        //patients where performer is the mcp
        $performer = $this->performer();
        $performerProID = $performer->pro->id;
        $isAdmin = ($performer->pro->pro_type === 'ADMIN');

        $keyNumbers  = [];

        $queryClients = $this->performer()->pro->getAccessibleClientsQuery();

        $keyNumbers['totalPatients'] = $queryClients->count();

        // patientNotSeenYet
        $patientNotSeenYet = $queryClients
            ->where(function ($query) use ($performer) {     // own patient and primary OB visit pending
                $query->where('mcp_pro_id', $performer->pro->id)
                    ->where('has_mcp_done_onboarding_visit', '!=', 'YES');
            })
//            ->orWhere(function ($query) {   // mcp of any client program and program OB pending
//                $query->where(function ($_query) {
//                    $_query->select(DB::raw('COUNT(id)'))
//                        ->from('client_program')
//                        ->whereColumn('client_id', 'client.id')
//                        ->where('has_mcp_done_onboarding_visit', '!=', 'YES');
//                }, '>=', 1);
//            })
            ->count();
        $keyNumbers['patientsNotSeenYet'] = $patientNotSeenYet;

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

        $keyNumbers['pendingBillsToSign'] = $pendingBillsToSign;

        $pendingNotesToSign = Note::where(function ($query) use ($performerProID) {
            $query->where('hcp_pro_id', $performerProID)->where('is_signed_by_hcp', false)->where('is_cancelled', false);;
        })
            ->orWhere(function ($query) use ($performerProID) {
                $query->where('ally_pro_id', $performerProID)->where('is_signed_by_ally', false)->where('is_cancelled', false);;
            })->count();
            $keyNumbers['pendingNotesToSign'] = $pendingNotesToSign;

        $pendingNotesToSignAllySigned = Note::where(function ($query) use ($performerProID) {
            $query->where('hcp_pro_id', $performerProID)->where('is_signed_by_hcp', false)->where('is_signed_by_ally', true)->where('is_cancelled', false);;
        })->count();
        $keyNumbers['pendingNotesToSignAllySigned'] = $pendingNotesToSignAllySigned;


        $signedNotesWithoutBills = Note::where(function ($query) use ($performerProID) {
            $query->where('hcp_pro_id', $performerProID)->where('is_signed_by_hcp', true)->where('is_cancelled', false);;
        })->whereDoesntHave('bills')->count();

        $keyNumbers['signedNotesWithoutBills'] = $signedNotesWithoutBills;

        // open tickets
        $keyNumbers['numOpenTickets'] = Ticket::where('is_open', true)
            ->where(function ($q) use ($performerProID) {
                $q->where('assigned_pro_id', $performerProID)
                    ->orWhere('manager_pro_id', $performerProID)
                    ->orWhere('ordering_pro_id', $performerProID)
                    ->orWhere('initiating_pro_id', $performerProID);
            })
            ->count();

        // unacknowledged cancelled bills for authed pro
        $keyNumbers['unacknowledgedCancelledBills'] = Bill::where('hcp_pro_id', $performerProID)
            ->where('is_cancelled', true)
            ->where('is_cancellation_acknowledged', false)
            ->count();

        // unacknowledged cancelled supply orders for authed pro
        $keyNumbers['unacknowledgedCancelledSupplyOrders'] = SupplyOrder::where('signed_by_pro_id', $performerProID)
            ->where('is_cancelled', true)
            ->where('is_cancellation_acknowledged', false)
            ->count();

        // unsigned supply orders created by authed pro
        $keyNumbers['unsignedSupplyOrders'] = SupplyOrder
            ::where('is_cancelled', false)
            ->where('is_signed_by_pro', false)
            ->whereRaw('created_by_session_id IN (SELECT id FROM app_session WHERE pro_id = ?)', [$performerProID])
            ->count();

        // patientsHavingBirthdayToday
        $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"] = '--';
        }

        //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);
            $reimbursement['nextPaymentDate'] = $nextPaymentDate->format('m/d/Y');
        } else {
            $nextPaymentDate = new \DateTime();
            $lastDayOfMonth = date('t', $today);
            $nextPaymentDate->setDate($todayYear, $todayMonth, $lastDayOfMonth);
            $reimbursement['nextPaymentDate'] = $nextPaymentDate->format('m/d/Y');
        }

        //expectedPay
        $expectedForHcp = DB::select(DB::raw("SELECT coalesce(SUM(hcp_expected_payment_amount),0) as expected_pay FROM bill WHERE hcp_pro_id = :performerProID  AND has_hcp_been_paid = false AND is_signed_by_hcp IS TRUE AND is_cancelled = false"), ['performerProID' => $performerProID])[0]->expected_pay;
        $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' => $performerProID])[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' => $performerProID])[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' => $performerProID])[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' => $performerProID])[0]->expected_pay;

        $totalExpectedAmount =  $expectedForHcp + $expectedForCm + $expectedForRme + $expectedForRmm + $expectedForNa;
        $reimbursement['nextPaymentAmount'] =  $totalExpectedAmount;

        $milliseconds = strtotime(date('Y-m-d')) . '000';

        // bills & claims
        $businessNumbers = [];

        // Notes with bills to resolve
        $businessNumbers['notesWithBillsToResolve'] = Note::where('is_cancelled', '!=', true)
            ->where('is_bill_closed', '!=', true)
            ->whereRaw('(SELECT count(id) FROM bill WHERE note_id = note.id AND is_cancelled = false AND is_verified = false) > 0')
            ->count();

        // Notes pending bill closure
        $businessNumbers['notesPendingBillingClosure'] = Note::where('is_cancelled', '!=', true)
            ->where('is_bill_closed', '!=', true)
            ->whereRaw('(SELECT count(id) FROM bill WHERE note_id = note.id AND (is_cancelled = true OR is_verified = true)) = 0')
            ->count();

        // incoming reports not signed
        $incomingReports = IncomingReport::where('hcp_pro_id', $performerProID)
            ->where('has_hcp_pro_signed', false)
            ->where('is_entry_error', false)
            ->orderBy('created_at', 'ASC')
            ->get();
        // erx, labs & imaging that are not closed
        $tickets = Ticket::where('ordering_pro_id', $performerProID)
            ->where('is_entry_error', false)
            ->where('is_open', true)
            ->orderBy('created_at', 'ASC')
            ->get();
        $supplyOrders = SupplyOrder::where('is_cleared_for_shipment', false)
            ->where('is_cancelled', false)
            ->whereRaw('created_by_session_id IN (SELECT id FROM app_session where pro_id = ?)', [$performer->pro->id])
            ->orderBy('created_at', 'ASC')
            ->get();

        $numERx = Ticket::where('ordering_pro_id', $performerProID)
            ->where('category', 'erx')
            ->where('is_entry_error', false)
            ->where('is_open', true)
            ->count();
        $numLabs = Ticket::where('ordering_pro_id', $performerProID)
            ->where('category', 'lab')
            ->where('is_entry_error', false)
            ->where('is_open', true)
            ->count();
        $numImaging = Ticket::where('ordering_pro_id', $performerProID)
            ->where('category', 'imaging')
            ->where('is_entry_error', false)
            ->where('is_open', true)
            ->count();
        $numSupplyOrders = SupplyOrder::where('is_cleared_for_shipment', false)
            ->where('is_cancelled', false)
            ->whereRaw('created_by_session_id IN (SELECT id FROM app_session where pro_id = ?)', [$performer->pro->id])
            ->count();

        $newMCPAssociations = ClientProChange
            ::where('new_pro_id', $performerProID)
            ->where('responsibility_type', 'MCP')
            ->whereNull('current_client_pro_change_decision_id')
            ->get();

        $newNAAssociations = ClientProChange
            ::where('new_pro_id', $performerProID)
            ->where('responsibility_type', 'DEFAULT_NA')
            ->whereNull('current_client_pro_change_decision_id')
            ->get();

        // unstamped client memos
        // for mcp
        $mcpClientMemos = 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.mcp_pro_id = {$performerProID} AND
      cm.mcp_stamp_id IS NULL
ORDER BY cm.created_at DESC
            ")
        );
        // for na
        $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 = {$performerProID} AND
      cm.default_na_stamp_id IS NULL
ORDER BY cm.created_at DESC
            ")
        );

        $naBillableSignedNotes = DB::select(DB::raw("
SELECT count(note.id) as na_billable_notes
FROM note
WHERE
        note.is_signed_by_hcp = TRUE AND
        note.ally_pro_id = :pro_id AND
        note.is_cancelled = FALSE AND
        (
            SELECT count(bill.id) 
            FROM bill 
            WHERE 
                  bill.is_cancelled = FALSE AND 
                  bill.generic_pro_id = :pro_id AND 
                  bill.note_id = note.id
        ) = 0
        "), ["pro_id" => $performerProID]);

        if(!$naBillableSignedNotes || !count($naBillableSignedNotes)) {
            $naBillableSignedNotes = 0;
        }
        else {
            $naBillableSignedNotes = $naBillableSignedNotes[0]->na_billable_notes;
        }

        $keyNumbers['naBillableSignedNotes'] = $naBillableSignedNotes;

        $keyNumbers['rmBillsToSign'] = Bill
            ::where('is_cancelled', false)
            ->where('cm_or_rm', 'RM')
            ->where(function ($q) use ($performerProID) {
                $q
                    ->where(function ($q2) use ($performerProID) {
                        $q2->where('hcp_pro_id', $performerProID)->where('is_signed_by_hcp', false);
                    })
                    ->orWhere(function ($q2) use ($performerProID) {
                        $q2->where('rme_pro_id', $performerProID)->where('is_signed_by_rme', false);
                    })
                    ->orWhere(function ($q2) use ($performerProID) {
                        $q2->where('rmm_pro_id', $performerProID)->where('is_signed_by_rmm', false);
                    })
                    ->orWhere(function ($q2) use ($performerProID) {
                        $q2->where('generic_pro_id', $performerProID)->where('is_signed_by_generic_pro', false);
                    });
            })
            ->count();

        $count = DB::select(
            DB::raw(
                "
SELECT count(client.id) as cnt FROM client join care_month on care_month.client_id = client.id
WHERE ((client.mcp_pro_id = {$performer->pro->id}) OR (client.rmm_pro_id = {$performer->pro->id})
          OR (client.rme_pro_id = {$performer->pro->id}) OR (client.default_na_pro_id = {$performer->pro->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.number_of_days_with_remote_measurements < 16 OR care_month.number_of_days_with_remote_measurements IS NULL)
"
            )
        );
        $keyNumbers['rmPatientsWithLT16MD'] = $count[0]->cnt;

        $count = DB::select(
            DB::raw(
                "
SELECT count(client.id) as cnt FROM client join care_month on care_month.client_id = client.id
WHERE ((client.mcp_pro_id = {$performer->pro->id}) OR (client.rmm_pro_id = {$performer->pro->id})
          OR (client.rme_pro_id = {$performer->pro->id}) OR (client.default_na_pro_id = {$performer->pro->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.number_of_days_with_remote_measurements >= 16 AND care_month.number_of_days_with_remote_measurements IS NOT NULL)
"
            )
        );
        $keyNumbers['rmPatientsWithGTE16MD'] = $count[0]->cnt;

        $count = DB::select(
            DB::raw(
                "
SELECT count(client.id) as cnt FROM client join care_month on care_month.client_id = client.id
WHERE ((client.mcp_pro_id = {$performer->pro->id}) OR (client.rmm_pro_id = {$performer->pro->id})
          OR (client.rme_pro_id = {$performer->pro->id}) OR (client.default_na_pro_id = {$performer->pro->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.has_anyone_interacted_with_client_about_rm_outside_note = TRUE AND care_month.has_anyone_interacted_with_client_about_rm_outside_note IS NOT NULL)
"
            )
        );
        $keyNumbers['rmPatientsWithWhomCommDone'] = $count[0]->cnt;

        $count = DB::select(
            DB::raw(
                "
SELECT count(client.id) as cnt FROM client join care_month on care_month.client_id = client.id
WHERE ((client.mcp_pro_id = {$performer->pro->id}) OR (client.rmm_pro_id = {$performer->pro->id})
          OR (client.rme_pro_id = {$performer->pro->id}) OR (client.default_na_pro_id = {$performer->pro->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.has_anyone_interacted_with_client_about_rm_outside_note = FALSE OR care_month.has_anyone_interacted_with_client_about_rm_outside_note IS NULL)
"
            )
        );
        $keyNumbers['rmPatientsWithWhomCommNotDone'] = $count[0]->cnt;

        // num measurements that need stamping
        $keyNumbers['measurementsToBeStamped'] = $this->performer()->pro->getUnstampedMeasurementsFromCurrentMonth(true, null, null);

        return view('app/dashboard', compact('keyNumbers', 'reimbursement', 'milliseconds',
            'businessNumbers',
            'incomingReports', 'tickets', 'supplyOrders',
            'numERx', 'numLabs', 'numImaging', 'numSupplyOrders',
            'newMCPAssociations', 'newNAAssociations',
            'mcpClientMemos', 'naClientMemos'));
    }

    public function dashboardMeasurementsTab(Request $request, $page = 1) {

        $performer = $this->performer();

        $myClientIDs = [];
        if ($performer->pro->pro_type != 'ADMIN') {
            $myClientIDs = $this->getMyClientIds();
            $myClientIDs = implode(", ", $myClientIDs);
        }

        $ifNotAdmin = " AND (
            client.mcp_pro_id = {$performer->pro->id}
            OR client.rmm_pro_id = {$performer->pro->id}
            OR client.rme_pro_id = {$performer->pro->id}
            OR client.physician_pro_id = {$performer->pro->id}
            OR client.id in (SELECT client_id FROM client_pro_access WHERE is_active AND pro_id = {$performer->pro->id})
            OR client.id in (SELECT client_id FROM appointment WHERE status NOT IN ('CANCELLED', 'ABANDONED') AND pro_id = {$performer->pro->id})
        )";

        $numMeasurements = DB::select(
            DB::raw(
                "
SELECT count(measurement.id) as cnt
FROM measurement
         join client on measurement.client_id = client.id
         join care_month on client.id = care_month.client_id
WHERE measurement.label NOT IN ('SBP', 'DBP')
  AND (measurement.is_cellular_zero = FALSE or measurement.is_cellular_zero IS NULL)
  AND measurement.is_removed IS FALSE
  AND measurement.ts IS NOT NULL
  AND measurement.client_bdt_measurement_id IS NOT NULL
  AND (measurement.status IS NULL OR (measurement.status <> 'ACK' AND measurement.status <> 'INVALID_ACK'))
  AND EXTRACT(MONTH from care_month.start_date) = EXTRACT(MONTH from now())
  AND EXTRACT(YEAR from care_month.start_date) = EXTRACT(YEAR from now())
" .
                (
                $performer->pro->pro_type != 'ADMIN' ? $ifNotAdmin : ''
  )
            )
        );

        $numMeasurements = $numMeasurements[0]->cnt;

        $measurements = DB::select(
            DB::raw(
                "
SELECT measurement.uid as uid,
       care_month.uid as care_month_uid,
       measurement.label,
       measurement.value,
       measurement.sbp_mm_hg,
       measurement.dbp_mm_hg,
       measurement.numeric_value,
       measurement.value_pulse,
       measurement.value_irregular,
       measurement.ts,
       client.mcp_pro_id,
       client.default_na_pro_id,
       client.rmm_pro_id,
       client.rme_pro_id,
       client.uid as client_uid,
       client.name_last,
       client.name_first,
       care_month.rm_total_time_in_seconds
FROM measurement
         join client on measurement.client_id = client.id
         join care_month on client.id = care_month.client_id
WHERE measurement.label NOT IN ('SBP', 'DBP')
  AND (measurement.is_cellular_zero = FALSE or measurement.is_cellular_zero IS NULL)
  AND measurement.is_removed IS FALSE
  AND measurement.ts IS NOT NULL
  AND measurement.client_bdt_measurement_id IS NOT NULL
  AND (measurement.status IS NULL OR (measurement.status <> 'ACK' AND measurement.status <> 'INVALID_ACK'))
  AND EXTRACT(MONTH from care_month.start_date) = EXTRACT(MONTH from now())
  AND EXTRACT(YEAR from care_month.start_date) = EXTRACT(YEAR from now())
" .
                (
                $performer->pro->pro_type != 'ADMIN' ? $ifNotAdmin : ''
  )
            ) .
            " ORDER BY measurement.ts DESC LIMIT 20 OFFSET " . (($page - 1) * 20)

        );

        return view('app.dashboard.measurements', compact('numMeasurements', 'measurements', 'page'));
    }

    public function dashboardAppointmentDates(Request $request, $from, $to) {
        $performer = $this->performer();
        $performerProID = $performer->pro->id;
        $isAdmin = ($performer->pro->pro_type === 'ADMIN');

        $results = DB::table('appointment')->select('raw_date')->distinct()->where("start_time", '>=', $from)->where("start_time", '<=', $to.' 23:59:00+00');

        if(!$isAdmin) {
            $results = $results->where("pro_id", $performerProID);
        }

        $results = $results->get();

        $dates = [];
        foreach ($results as $result) {
            // $dates[] = strtotime($result->raw_date) . '000';
            $dates[] = $result->raw_date;
        }

//        foreach ($results as $result) {
//            $results->dateYMD = date('Y-m-d', strtotime($result->raw_date));
//        }

        return json_encode($dates);
    }

    public function dashboardAppointments(Request $request, $from, $to) {

        $performer = $this->performer();
        $performerProID = $performer->pro->id;
        $isAdmin = ($performer->pro->pro_type === 'ADMIN');

        // $appointments = Appointment::where("start_time", '>=', $from)->where("start_time", '<=', $to.' 23:59:00+00');
        $appointments = Appointment::where("raw_date", '=', $from);

        if(!$isAdmin) {
            $appointments = $appointments->where("pro_id", $performerProID);
        }

        $appointments = $appointments
            ->orderBy('start_time', 'asc')
            ->get();

        foreach ($appointments as $appointment) {
            $date = explode(" ", $appointment->start_time)[0];
            $appointment->milliseconds = strtotime($date) . '000';
            $appointment->newStatus = $appointment->status;

            $appointment->dateYMD = date('Y-m-d', strtotime($appointment->raw_date));
            $appointment->clientName = $appointment->client->displayName();
            $appointment->clientInitials = substr($appointment->client->name_first, 0, 1) . substr($appointment->client->name_last, 0, 1);
            $appointment->isClientShadowOfPro = $appointment->client->shadow_pro_id ? true : false;
            $appointment->proInitials = substr($appointment->pro->name_first, 0, 1) . substr($appointment->pro->name_last, 0, 1);
            $appointment->friendlyStartTime = friendly_time($appointment->raw_start_time);
            $appointment->friendlyEndTime = friendly_time($appointment->raw_end_time);
            $appointment->clientSummary = friendly_date_time($appointment->client->dob, false) . ' (' .
                $appointment->client->age_in_years . ' y.o' .
                ($appointment->client->sex ? ' ' . $appointment->client->sex : '') .
                ')';

            $appointment->started = false;
            $appointment->inHowManyHours = date_diff(date_create('now'), date_create($appointment->start_time), false)
                ->format('%R%h h, %i m');
            if ($appointment->inHowManyHours[0] === '-') {
                $appointment->inHowManyHours = substr($appointment->inHowManyHours, 1) . ' ago';
                $appointment->started = true;
            } else {
                $appointment->inHowManyHours = 'Appt. in ' . substr($appointment->inHowManyHours, 1);
            }
            $appointment->clientUid = $appointment->client->uid;
            $appointment->proUid = $appointment->pro->uid;
            $appointment->proName = $appointment->pro->displayName();

            unset($appointment->client);
            unset($appointment->pro);
            unset($appointment->detail_json);
        }

        return json_encode($appointments);
    }

    public function dashboardMeasurements(Request $request, $filter) {
        $measurements = $this->performer()->pro->getMeasurements($filter === 'NEED_ACK');
        return json_encode($measurements);
    }

    public function patients(Request $request, $filter = '')
    {

        $performer = $this->performer();
        $query = $performer->pro->getAccessibleClientsQuery();

        $q = trim($request->input('q'));
        if(!empty($q)) {
            $query = $query->where(function ($query) use ($q) {
                $query->where('name_first', 'ILIKE', "%$q%")
                    ->orWhere('name_last', 'ILIKE', "%$q%")
                    ->orWhere('email_address', 'ILIKE', "%$q%")
                    ->orWhere('tags', 'ILIKE', "%$q%");
            });
        }

        switch ($filter) {
            case 'not-yet-seen':
                $query = $query
                    ->where(function ($query) use ($performer) {
                        $query
                            ->where(function ($query) use ($performer) {     // own patient and primary OB visit pending
                                $query->where('mcp_pro_id', $performer->pro->id)
                                    ->where('has_mcp_done_onboarding_visit', '<>', 'YES');
                            })
                            ->orWhere(function ($query) use ($performer) {   // mcp of any client program and program OB pending
                                $query->select(DB::raw('COUNT(id)'))
                                    ->from('client_program')
                                    ->whereColumn('client_id', 'client.id')
                                    ->where('mcp_pro_id', $performer->pro->id)
                                    ->where('has_mcp_done_onboarding_visit', '<>', 'YES');
                            }, '>=', 1);
                    });
                break;

            case 'having-birthday-today':
                $query = $query
                    ->whereRaw('EXTRACT(DAY from dob) = ?', [date('d')])
                    ->whereRaw('EXTRACT(MONTH from dob) = ?', [date('m')]);
                break;

                // more cases can be added as needed
            default:
                break;
        }
        $patients = $query->orderBy('id', 'desc')->paginate(50);

        // patient acquisition chart (admin only)
        $patientAcquisitionData = null;
        if($performer->pro->pro_type === 'ADMIN') {
            $startDate = date_sub(date_create(), date_interval_create_from_date_string("1 month"));
            $startDate = date_format($startDate, "Y-m-d");
            $patientAcquisitionData = DB::select(DB::raw(
                "SELECT count(id) as count, DATE(created_at at time zone 'utc' at time zone 'est') as date " .
                "FROM client " .
                "WHERE shadow_pro_id IS NULL " .
                "GROUP BY DATE(created_at at time zone 'utc' at time zone 'est') " .
                "ORDER BY DATE(created_at at time zone 'utc' at time zone 'est') DESC " .
                "LIMIT 30"));
        }

        return view('app/patients', compact('patients', 'filter', 'patientAcquisitionData'));

    }

    public function patientsSuggest(Request $request)
    {

        $pro = $this->pro;
        $term = $request->input('term') ? trim($request->input('term')) : '';
        if (empty($term)) return '';

        // if multiple words in query, check for all (max 2)
        $term2 = '';
        if(strpos($term, ' ') !== FALSE) {
            $terms = explode(' ', $term);
            $term = trim($terms[0]);
            $term2 = trim($terms[1]);
        }

        $clientQuery= Client::whereNull('shadow_pro_id')
            ->where(function ($q) use ($term) {
                $q->where('name_first', 'ILIKE', '%' . $term . '%')
                    ->orWhere('name_last', 'ILIKE', '%' . $term . '%')
                    ->orWhere('cell_number', 'ILIKE', '%' . $term . '%')
                    ->orWhere('phone_home', 'ILIKE', '%' . $term . '%');
            });

        if(!empty($term2)) {
            $clientQuery = $clientQuery->where(function ($q) use ($term2) {
                $q->where('name_first', 'ILIKE', '%' . $term2 . '%')
                    ->orWhere('name_last', 'ILIKE', '%' . $term2 . '%')
                    ->orWhere('cell_number', 'ILIKE', '%' . $term2 . '%')
                    ->orWhere('phone_home', 'ILIKE', '%' . $term2 . '%');
            });
        }

        if(!($pro->pro_type === 'ADMIN' && $pro->can_see_any_client_via_search)) {
            $clientQuery->where(function ($q) use ($pro) {
                if($pro->pro_type === 'ADMIN') {
                    $q->whereIn('id', $pro->getMyClientIds(true))->orWhereNull('mcp_pro_id');
                }
                else {
                    $q->whereIn('id', $pro->getMyClientIds(true));
                }
            });
        }

        $clients = $clientQuery->get();
        return view('app/patient-suggest', compact('clients'));
    }

    public function pharmacySuggest(Request $request)
    {
        $term = $request->input('term') ? trim($request->input('term')) : '';
        if (empty($term)) return '';
        $term = strtolower($term);
        $pharmacies = Facility::where('facility_type', 'Pharmacy')
            ->where(function ($q) use ($term) {
                $q->orWhereRaw('LOWER(name::text) LIKE ?', ['%' . $term . '%'])
                    ->orWhereRaw('LOWER(address_line1::text) LIKE ?', ['%' . $term . '%'])
                    ->orWhereRaw('LOWER(address_line2::text) LIKE ?', ['%' . $term . '%'])
                    ->orWhereRaw('LOWER(address_city::text) LIKE ?', ['%' . $term . '%'])
                    ->orWhereRaw('LOWER(address_state::text) LIKE ?', ['%' . $term . '%'])
                    ->orWhereRaw('LOWER(phone::text) LIKE ?', ['%' . $term . '%'])
                    ->orWhereRaw('LOWER(address_zip::text) LIKE ?', ['%' . $term . '%']);
            });
        if($request->input('city')) {
            $pharmacies = $pharmacies->whereRaw('LOWER(address_city::text) LIKE ?', ['%' . strtolower($request->input('city')) . '%']);
        }
        if($request->input('state')) {
            $pharmacies = $pharmacies->whereRaw('LOWER(address_state::text) LIKE ?', ['%' . strtolower($request->input('state')) . '%']);
        }
        if($request->input('zip')) {
            $pharmacies = $pharmacies->whereRaw('LOWER(address_zip::text) LIKE ?', ['%' . strtolower($request->input('zip')) . '%']);
        }
        $pharmacies = $pharmacies
            ->orderBy('name', 'asc')
            ->orderBy('address_line1', 'asc')
            ->orderBy('address_city', 'asc')
            ->orderBy('address_state', 'asc')
            ->get();
        return view('app/pharmacy-suggest', compact('pharmacies'));
    }

    public function proSuggest(Request $request) {
        $term = $request->input('term') ? trim($request->input('term')) : '';
        if (empty($term)) return '';
        $term = strtolower($term);
        $pros = Pro::where(function ($q) use ($term) {
            $q->orWhereRaw('LOWER(name_first::text) LIKE ?', ['%' . $term . '%'])
                ->orWhereRaw('LOWER(name_last::text) LIKE ?', ['%' . $term . '%'])
                ->orWhereRaw('cell_number LIKE ?', ['%' . $term . '%']);
        });
        if($this->performer->pro && $this->performer->pro->pro_type != 'ADMIN'){
            $accessiblePros = ProProAccess::where('owner_pro_id', $this->performer->pro->id);
            $accessibleProIds = [];
            foreach($accessiblePros as $accessiblePro){
                $accessibleProIds[] = $accessiblePro->id;
            }
            $accessibleProIds[] = $this->performer->pro->id;
            $pros->whereIn('id', $accessibleProIds);
        }
        $suggestedPros = $pros->orderBy('name_last')->orderBy('name_first')->get();

        // for calendar select2
        if($request->input('json')) {
            $jsonPros = $suggestedPros->map(function($_pro) {
                return [
                    "uid" => $_pro->uid,
                    "id" => $_pro->id,
                    "text" => $_pro->displayName(),
                    "initials" => $_pro->initials(),
                ];
            });
            return json_encode([
                "results" => $jsonPros
            ]);
        }

        return view('app/pro-suggest', compact('suggestedPros'));
    }

    public function proDisplayName(Request $request, Pro $pro) {
        return $pro ? $pro->displayName() : '';
    }

    public function unmappedSMS(Request $request, $filter = '')
    {
        $proID = $this->performer()->pro->id;
        if ($this->performer()->pro->pro_type === 'ADMIN') {
            $query = Client::where('id', '>', 0);
        } else {
            $query = Client::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)
                    ->orWhereRaw('id IN (SELECT client_id FROM client_pro_access WHERE is_active AND pro_id = ?)', [$proID]);
            });
        }
        $patients = $query->orderBy('name_last', 'asc')->orderBy('name_first', 'asc')->get();
        $unmappedSMS = ClientSMS::where('client_id', null)->where('incoming_or_outgoing', 'INCOMING')->get();
        return view('app/unmapped-sms', compact('unmappedSMS', 'patients'));
    }

    public function newPatient(Request $request)
    {
        $mbPayers = MBPayer::all();
        return view('app/new-patient', compact('mbPayers'));
    }

    public function newNonMcnPatient(Request $request)
    {
        $mbPayers = MBPayer::all();
        return view('app/new-non-mcn-patient', compact('mbPayers'));
    }

    public function mc(Request $request, $fragment = "")
    {
        $page = "/";
        if ($fragment) {
            $page = '/' . $fragment;
        }
        return view('app/mc', compact('page'));
    }

    public function blank(Request $request)
    {
        return view('app/blank');
    }

    public function noteTemplateSet(Request $request, $section, $template)
    {
        return view('app/patient/note/_template', [
            "sectionInternalName" => $section,
            "templateName" => $template
        ]);
    }

    public function noteExamTemplateSet(Request $request, $exam, $template)
    {
        return view('app/patient/note/_template-exam', [
            "exam" => $exam,
            "sectionInternalName" => 'exam-' . $exam . '-detail',
            "templateName" => $template
        ]);
    }

    public function logInAs(Request $request)
    {   
        if($this->pro->pro_type != 'ADMIN'){
            return redirect()->to(route('dashboard'));
        }

        $pros =  Pro::where('pro_type', '!=', 'ADMIN');

        if($request->input('q')) {
            $nameQuery = '%' . $request->input('q') . '%';
            $pros = $pros->where(function ($query) use ($nameQuery) {
                $query->where('name_first', 'ILIKE', $nameQuery)
                    ->orWhere('name_last', 'ILIKE', $nameQuery)
                    ->orWhere('email_address', 'ILIKE', $nameQuery)
                    ->orWhere('cell_number', 'ILIKE', $nameQuery);
            });
        }

        if($request->input('sort') && $request->input('dir')) {
            $pros = $pros->orderBy($request->input('sort'), $request->input('dir'));
        }
        else {
            $pros = $pros->orderBy('name_last', 'asc');
        }

        $pros = $pros->paginate(20);

        return view('app/log-in-as', ['logInAsPros' => $pros]);
    }

    public function processLogInAs(Request $request)
    {

        $api = new Backend();

        try {
            $apiResponse = $api->post('session/proLogInAs', [
                'proUid' => $request->post('proUid')
            ],
            [
                'sessionKey'=>$this->performer()->session_key
            ]);

            $data = json_decode($apiResponse->getContents());

            if (!property_exists($data, 'success') || !$data->success) {
                return redirect()->to(route('log-in-as'))->with('message', $data->message)
                    ->withInput($request->input());
            }

            Cookie::queue('sessionKey', $data->data->sessionKey);

            return redirect('/mc');
        } catch (\Exception $e) {
            return redirect()->to(route('log-in-as'))
                ->with('message', 'Unable to process your request at the moment. Please try again later.')
                ->withInput($request->input());
        }
    }

    public function backToAdminPro(Request $request){
        $adminPerformerId = $this->performer->logged_in_as_pro_from_admin_pro_app_session_id;
        $adminPerformer = AppSession::where('id', $adminPerformerId)->first();
        $url = "/session/pro_log_in_with_session_key/".$adminPerformer->session_key;
        $api = new Backend();
        try {
            $apiResponse = $api->post($url, []);
            $data = json_decode($apiResponse->getContents());

            if (!property_exists($data, 'success') || !$data->success) {
                return redirect()->to(route('logout'));
            }


            Cookie::queue('sessionKey', $data->data->sessionKey);

            return redirect(route('dashboard'));

        } catch (\Exception $e) {
            return redirect(route('dashboard'));
        }
    }

    public function getTicket(Request $request, Ticket $ticket) {
        $ticket->data = json_decode($ticket->data);
//        $ticket->created_at = friendly_date_time($ticket->created_at);
        $ticket->assignedPro;
        $ticket->managerPro;
        $ticket->orderingPro;
        $ticket->initiatingPro;
        return json_encode($ticket);
    }

    public function genericBill(Request $request, $entityType, $entityUid) {
        $patient = null;
        if ($entityType && $entityUid) {
            try {
                $entityClass = "\\App\\Models\\" . $entityType;
                $entity = $entityClass::where('uid', $entityUid)->first();
                if ($entity->client) {
                    $patient = $entity->client;
                }
            } catch (\Exception $e) {
            }
        }
        return view('app.generic-bills.inline', ['class' => 'p-3 border-top mt-3', 'entityType' => $entityType, 'entityUid' => $entityUid, 'patient' => $patient]);
    }
}