stagfe2/app/Http/Controllers/HomeController.php

<?php

namespace App\Http\Controllers;

use App\Lib\Backend;
use App\Models\Appointment;
use App\Models\AppointmentConfirmationDecision;
use App\Models\AppSession;
use App\Models\CareMonth;
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 App\Models\Erx;
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;
use App\Models\OutgoingEmailTemplate;

class HomeController extends Controller
{

    public function nop() {
        return json_encode(["success" => true]);
    }

    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_MCP(Request $request){


        $keyNumbers = [];

        // Patients // SELECT * FROM client WHERE mcp_pro_id = :me.id;
        // New Patients Awaiting Visit // SELECT * FROM client WHERE mcp_pro_id = :me.id AND hasMcpDoneOnboardingVisit != 'YES';
        // Notes Pending Signature // SELECT * FROM note WHERE hcp_pro_id = :me.id AND is_cancelled IS NOT TRUE AND has_hcp_signed IS NOT TRUE;
        // Notes Pending Billing // SELECT * FROM note WHERE hcp_pro_id = :me.id AND is_cancelled IS NOT TRUE AND has_hcp_signed IS TRUE AND is_billing_marked_done IS FALSE;
        // Reports Pending Signature // SELECT * FROM incoming_report WHERE hcp_pro_id = :me.id AND isEntryError IS NOT TRUE AND hasHcpProSigned IS NOT TRUE;
        // Patients w/o Appointments // SELECT * FROM client WHERE mcp_pro_id = :me.id AND client.next_mcp_appointment_date < today();
        // Patients Overdue for Visit // SELECT * FROM client WHERE mcp_pro_id = :me.id AND client.next_expected_mcp_visit_date < today();
        // Cancelled Appts. Pending Review // SELECT * FROM appointment WHERE hcp_pro_id = :me.id AND status = 'REJECTED' AND wasAcknowledgedByAppointmentPro IS NOT TRUE;
        // Cancelled Bills Pending Review // SELECT * FROM bill WHERE bill_service_type = 'NOTE' AND is_cancelled IS TRUE AND isCancellationAcknowledged IS FALSE;
        // Cancelled Supply Orders Pending Review // SELECT * FROM supply_order WHERE signed_by_pro_id = :me.id AND is_cancelled IS TRUE AND isCancellationAcknowledged IS NOT TRUE;
        // ERx & Orders Pending Signature // SELECT * FROM erx WHERE hcp_pro_id = :me.id AND pro_declared_status <> 'CANCELLED' AND hasHcpProSigned IS NOT TRUE;
        // Supply Orders Pending Signature // SELECT supply_order.id FROM supply_order WHERE signed_by_pro_id IS NOT TRUE AND is_cancelled IS NOT TRUE AND created_by_pro_id = :me.id;

        $performer = $this->performer();
        $pro = $performer->pro;
        $performerProID = $performer->pro->id;

        $keyNumbers  = [];

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

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

        // notes pending mcp sign (applicable to dnas only)
        $pendingNotesToSignMCP = Note
            ::where(function ($query) use ($performerProID) {
                $query->where('ally_pro_id', $performerProID)->where('is_signed_by_hcp', false)->where('is_cancelled', false)->where('is_core_note', false);
            })
            ->count();
        $keyNumbers['$pendingNotesToSignMCP'] = $pendingNotesToSignMCP;

        $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)->where('is_core_note', 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();

        $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
      AND (is_admin_only IS FALSE OR is_admin_only IS NULL)
ORDER BY cm.created_at DESC
OFFSET 0 LIMIT 10
            ")
        );
        $mcpClientMemosCount = DB::select(
            DB::raw("
SELECT count(c.uid)
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
      AND (is_admin_only IS FALSE OR is_admin_only IS NULL)
            ")
        );
        if($mcpClientMemosCount && count($mcpClientMemosCount)) {
            $mcpClientMemosCount = $mcpClientMemosCount[0]->count;
        }

        // 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
            ")
        );

        $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;

        if($performer->pro->pro_type === 'ADMIN') {

            // patients without coverage information
            $keyNumbers['patientsWithoutCoverageInformation'] = DB::select(DB::raw("
SELECT count(DISTINCT (cl.id)) as cnt
FROM client cl
WHERE cl.shadow_pro_id IS NULL AND cl.effective_client_primary_coverage_id IS NULL -- no coverage record"
            ))[0]->cnt;

            // patients pending coverage verification
            $keyNumbers['patientsPendingCoverageVerification'] = DB::select(DB::raw("
SELECT count(DISTINCT (cl.id)) as cnt
FROM client cl
         LEFT JOIN client_primary_coverage cpc ON cl.effective_client_primary_coverage_id = cpc.id
WHERE cl.shadow_pro_id IS NULL
    AND (cl.effective_client_primary_coverage_id IS NOT NULL -- coverage exists, but status is null or unknown
    AND (
               (cpc.plan_type = 'MEDICARE' AND (cpc.is_partbprimary = 'UNKNOWN' OR cpc.is_partbprimary IS NULL))
               OR
               (cpc.plan_type != 'MEDICARE' AND
                (cpc.manual_determination_category = 'UNKNOWN' OR cpc.manual_determination_category IS NULL))
           ))"
            ))[0]->cnt;

        }

        $incomingSmsMessagesPendingReply = DB::select("
            SELECT cs.* ,c.uid as client_uid, c.name_first as client_name_first, c.name_last as client_name_last FROM client_sms cs LEFT JOIN client c ON c.id = cs.client_id
            WHERE cs.is_reply_needed = 'YES' AND c.mcp_pro_id = :mcp_pro_id AND incoming_or_outgoing = 'INCOMING'
            AND (cs.created_at > c.last_sms_sent_to_client_at OR c.last_sms_sent_to_client_at IS NULL)
            ORDER BY created_at DESC
        ", ['mcp_pro_id' => $performer->pro->id]);

        $careMonthsWithMeasurementsPendingStamping = CareMonth::select('id')
            ->where('mcp_pro_id', $this->performer->pro->id)
            ->where('rm_num_measurements_not_stamped_by_mcp', '>', 0)
            ->whereNotNull('rm_num_measurements_not_stamped_by_mcp')
            ->orderBy('created_at', 'DESC')
            ->get()
            ->map(function($_x) {
                return $_x->id;
            })
            ->toArray();

        $measurementsPendingStamping = Measurement::whereIn('care_month_id', $careMonthsWithMeasurementsPendingStamping)
            ->orderBy('created_at', 'DESC')
            ->whereNotNull('ts')
            ->whereNotIn('label', ['SBP', 'DBP'])
            ->where('is_cellular_zero', '<>', true)
            ->where('is_active', true)
            ->where('has_been_stamped_by_mcp', false)
            ->whereNotNull('client_bdt_measurement_id')
            ->paginate(15);

        $flaggedMeasurementsPendingStamping = Measurement::whereIn('care_month_id', $careMonthsWithMeasurementsPendingStamping)
            ->orderBy('created_at', 'DESC')
            ->whereNotNull('ts')
            ->whereNotIn('label', ['SBP', 'DBP'])
            ->where('is_cellular_zero', '<>', true)
            ->where('is_active', true)
            ->where('has_been_stamped_by_mcp', false)
            ->whereRaw("(color = 'RED' OR color = 'YELLOW')")
            ->whereNotNull('client_bdt_measurement_id')
            ->paginate(15);

        return view('app/dashboard-mcp', compact('keyNumbers', 'reimbursement', 'milliseconds',
            'businessNumbers',
            'incomingReports', 'tickets', 'supplyOrders',
            'numLabs', 'numImaging', 'numSupplyOrders',
            'newMCPAssociations', 'newNAAssociations',
            'measurementsPendingStamping', 'flaggedMeasurementsPendingStamping',
            'careMonthsWithMeasurementsPendingStamping',
            'mcpClientMemos', 'mcpClientMemosCount', 'naClientMemos', 'incomingSmsMessagesPendingReply'));
    }

    public function dashboard_HCP(Request $request){
        //TODO provide hcp specific data. Currently is based on MCP context
        $keyNumbers = [];

        // Patients // SELECT * FROM client WHERE mcp_pro_id = :me.id;
        // New Patients Awaiting Visit // SELECT * FROM client WHERE mcp_pro_id = :me.id AND hasMcpDoneOnboardingVisit != 'YES';
        // Notes Pending Signature // SELECT * FROM note WHERE hcp_pro_id = :me.id AND is_cancelled IS NOT TRUE AND has_hcp_signed IS NOT TRUE;
        // Notes Pending Billing // SELECT * FROM note WHERE hcp_pro_id = :me.id AND is_cancelled IS NOT TRUE AND has_hcp_signed IS TRUE AND is_billing_marked_done IS FALSE;
        // Reports Pending Signature // SELECT * FROM incoming_report WHERE hcp_pro_id = :me.id AND isEntryError IS NOT TRUE AND hasHcpProSigned IS NOT TRUE;
        // Patients w/o Appointments // SELECT * FROM client WHERE mcp_pro_id = :me.id AND client.next_mcp_appointment_date < today();
        // Patients Overdue for Visit // SELECT * FROM client WHERE mcp_pro_id = :me.id AND client.next_expected_mcp_visit_date < today();
        // Cancelled Appts. Pending Review // SELECT * FROM appointment WHERE hcp_pro_id = :me.id AND status = 'REJECTED' AND wasAcknowledgedByAppointmentPro IS NOT TRUE;
        // Cancelled Bills Pending Review // SELECT * FROM bill WHERE bill_service_type = 'NOTE' AND is_cancelled IS TRUE AND isCancellationAcknowledged IS FALSE;
        // Cancelled Supply Orders Pending Review // SELECT * FROM supply_order WHERE signed_by_pro_id = :me.id AND is_cancelled IS TRUE AND isCancellationAcknowledged IS NOT TRUE;
        // ERx & Orders Pending Signature // SELECT * FROM erx WHERE hcp_pro_id = :me.id AND pro_declared_status <> 'CANCELLED' AND hasHcpProSigned IS NOT TRUE;
        // Supply Orders Pending Signature // SELECT supply_order.id FROM supply_order WHERE signed_by_pro_id IS NOT TRUE AND is_cancelled IS NOT TRUE AND created_by_pro_id = :me.id;

        $performer = $this->performer();
        $pro = $performer->pro;
        $performerProID = $performer->pro->id;

        $keyNumbers  = [];

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

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

        // notes pending mcp sign (applicable to dnas only)
        $pendingNotesToSignMCP = Note
            ::where(function ($query) use ($performerProID) {
                $query->where('ally_pro_id', $performerProID)->where('is_signed_by_hcp', false)->where('is_cancelled', false)->where('is_core_note', false);
            })
            ->count();
        $keyNumbers['$pendingNotesToSignMCP'] = $pendingNotesToSignMCP;

        $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)->where('is_core_note', 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();

        $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
      AND (is_admin_only IS FALSE OR is_admin_only IS NULL)
ORDER BY cm.created_at DESC
OFFSET 0 LIMIT 10
            ")
        );
        $mcpClientMemosCount = DB::select(
            DB::raw("
SELECT count(c.uid)
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
      AND (is_admin_only IS FALSE OR is_admin_only IS NULL)
            ")
        );
        if($mcpClientMemosCount && count($mcpClientMemosCount)) {
            $mcpClientMemosCount = $mcpClientMemosCount[0]->count;
        }

        // 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
            ")
        );

        $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;

        if($performer->pro->pro_type === 'ADMIN') {

            // patients without coverage information
            $keyNumbers['patientsWithoutCoverageInformation'] = DB::select(DB::raw("
SELECT count(DISTINCT (cl.id)) as cnt
FROM client cl
WHERE cl.shadow_pro_id IS NULL AND cl.effective_client_primary_coverage_id IS NULL -- no coverage record"
            ))[0]->cnt;

            // patients pending coverage verification
            $keyNumbers['patientsPendingCoverageVerification'] = DB::select(DB::raw("
SELECT count(DISTINCT (cl.id)) as cnt
FROM client cl
         LEFT JOIN client_primary_coverage cpc ON cl.effective_client_primary_coverage_id = cpc.id
WHERE cl.shadow_pro_id IS NULL
    AND (cl.effective_client_primary_coverage_id IS NOT NULL -- coverage exists, but status is null or unknown
    AND (
               (cpc.plan_type = 'MEDICARE' AND (cpc.is_partbprimary = 'UNKNOWN' OR cpc.is_partbprimary IS NULL))
               OR
               (cpc.plan_type != 'MEDICARE' AND
                (cpc.manual_determination_category = 'UNKNOWN' OR cpc.manual_determination_category IS NULL))
           ))"
            ))[0]->cnt;

        }

        $incomingSmsMessagesPendingReply = DB::select("
            SELECT cs.* ,c.uid as client_uid, c.name_first as client_name_first, c.name_last as client_name_last FROM client_sms cs LEFT JOIN client c ON c.id = cs.client_id
            WHERE cs.is_reply_needed = 'YES' AND c.mcp_pro_id = :mcp_pro_id AND incoming_or_outgoing = 'INCOMING'
            AND (cs.created_at > c.last_sms_sent_to_client_at OR c.last_sms_sent_to_client_at IS NULL)
            ORDER BY created_at DESC
        ", ['mcp_pro_id' => $performer->pro->id]);

        $careMonthsWithMeasurementsPendingStamping = CareMonth::select('id')
            ->where('mcp_pro_id', $this->performer->pro->id)
            ->where('rm_num_measurements_not_stamped_by_mcp', '>', 0)
            ->whereNotNull('rm_num_measurements_not_stamped_by_mcp')
            ->orderBy('created_at', 'DESC')
            ->get()
            ->map(function($_x) {
                return $_x->id;
            })
            ->toArray();

        $measurementsPendingStamping = Measurement::whereIn('care_month_id', $careMonthsWithMeasurementsPendingStamping)
            ->orderBy('created_at', 'DESC')
            ->whereNotNull('ts')
            ->whereNotIn('label', ['SBP', 'DBP'])
            ->where('is_cellular_zero', '<>', true)
            ->where('is_active', true)
            ->where('has_been_stamped_by_mcp', false)
            ->whereNotNull('client_bdt_measurement_id')
            ->paginate(15);

        return view('app/dashboard-hcp', compact('keyNumbers', 'reimbursement', 'milliseconds',
            'businessNumbers',
            'incomingReports', 'tickets', 'supplyOrders',
            'numLabs', 'numImaging', 'numSupplyOrders',
            'newMCPAssociations', 'newNAAssociations',
            'measurementsPendingStamping', 'careMonthsWithMeasurementsPendingStamping',
            'mcpClientMemos', 'mcpClientMemosCount', 'naClientMemos', 'incomingSmsMessagesPendingReply'));
    }

    public function dashboard_DNA(Request $request){
        $performer = $this->performer();
        $pro = $performer->pro;
        $performerProID = $performer->pro->id;

        $milliseconds = strtotime(date('Y-m-d')) . '000'; //required by the calendar
        return view('app/dashboard-dna', compact( 'milliseconds'));
    }

    public function dashboard_ADMIN(Request $request){
        $keyNumbers = [];

        // Patients // SELECT * FROM client WHERE mcp_pro_id = :me.id;
        // New Patients Awaiting Visit // SELECT * FROM client WHERE mcp_pro_id = :me.id AND hasMcpDoneOnboardingVisit != 'YES';
        // Notes Pending Signature // SELECT * FROM note WHERE hcp_pro_id = :me.id AND is_cancelled IS NOT TRUE AND has_hcp_signed IS NOT TRUE;
        // Notes Pending Billing // SELECT * FROM note WHERE hcp_pro_id = :me.id AND is_cancelled IS NOT TRUE AND has_hcp_signed IS TRUE AND is_billing_marked_done IS FALSE;
        // Reports Pending Signature // SELECT * FROM incoming_report WHERE hcp_pro_id = :me.id AND isEntryError IS NOT TRUE AND hasHcpProSigned IS NOT TRUE;
        // Patients w/o Appointments // SELECT * FROM client WHERE mcp_pro_id = :me.id AND client.next_mcp_appointment_date < today();
        // Patients Overdue for Visit // SELECT * FROM client WHERE mcp_pro_id = :me.id AND client.next_expected_mcp_visit_date < today();
        // Cancelled Appts. Pending Review // SELECT * FROM appointment WHERE hcp_pro_id = :me.id AND status = 'REJECTED' AND wasAcknowledgedByAppointmentPro IS NOT TRUE;
        // Cancelled Bills Pending Review // SELECT * FROM bill WHERE bill_service_type = 'NOTE' AND is_cancelled IS TRUE AND isCancellationAcknowledged IS FALSE;
        // Cancelled Supply Orders Pending Review // SELECT * FROM supply_order WHERE signed_by_pro_id = :me.id AND is_cancelled IS TRUE AND isCancellationAcknowledged IS NOT TRUE;
        // ERx & Orders Pending Signature // SELECT * FROM erx WHERE hcp_pro_id = :me.id AND pro_declared_status <> 'CANCELLED' AND hasHcpProSigned IS NOT TRUE;
        // Supply Orders Pending Signature // SELECT supply_order.id FROM supply_order WHERE signed_by_pro_id IS NOT TRUE AND is_cancelled IS NOT TRUE AND created_by_pro_id = :me.id;

        $performer = $this->performer();
        $pro = $performer->pro;
        $performerProID = $performer->pro->id;

        $keyNumbers  = [];

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

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

        // notes pending mcp sign (applicable to dnas only)
        $pendingNotesToSignMCP = Note
            ::where(function ($query) use ($performerProID) {
                $query->where('ally_pro_id', $performerProID)->where('is_signed_by_hcp', false)->where('is_cancelled', false)->where('is_core_note', false);
            })
            ->count();
        $keyNumbers['$pendingNotesToSignMCP'] = $pendingNotesToSignMCP;

        $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)->where('is_core_note', 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();

        $numLabs = Ticket::where('ordering_pro_id', $performerProID)
            ->where('category', 'lab')
            ->where('is_entry_error', false)
            ->where('is_open', true)
            ->count();
        $ERxsPendingSignature = Erx::where('pro_declared_status', '<>', 'CANCELLED')
        ->where('has_hcp_pro_signed', '<>', true)->get();
        

        $numERx = count($ERxsPendingSignature);

        $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 = [];
        if($request->input('mcpas')) {
            $newMCPAssociations = ClientProChange
                ::where('new_pro_id', $performerProID)
                ->where('responsibility_type', 'MCP')
                ->whereNull('current_client_pro_change_decision_id')
                ->get();
        }
        else {
            $newMCPAssociations = ClientProChange
                ::where('new_pro_id', $performerProID)
                ->where('responsibility_type', 'MCP')
                ->whereNull('current_client_pro_change_decision_id')
                ->count();
        }

        $newNAAssociations = [];
        if($request->input('naas')) {
            $newNAAssociations = ClientProChange
                ::where('new_pro_id', $performerProID)
                ->where('responsibility_type', 'DEFAULT_NA')
                ->whereNull('current_client_pro_change_decision_id')
                ->get();
        }
        else {
            $newNAAssociations = ClientProChange
                ::where('new_pro_id', $performerProID)
                ->where('responsibility_type', 'DEFAULT_NA')
                ->whereNull('current_client_pro_change_decision_id')
                ->count();
        }

        $proApptUpdates = AppointmentConfirmationDecision
            ::select('appointment_confirmation_decision.uid', 'client.name_first', 'client.name_last', 'appointment.start_time')
            ->rightJoin('appointment', 'appointment.id', '=', 'appointment_confirmation_decision.appointment_id')
            ->rightJoin('client', 'client.id', '=', 'appointment.client_id')
            ->where('appointment_confirmation_decision.was_acknowledged_by_appointment_pro', false)
            ->where('appointment.status', '!=', 'CREATED')
            ->where('appointment.status', '!=', 'COMPLETED')
            ->where('appointment.status', '!=', 'ABANDONED')
            ->where('appointment.pro_id', $performerProID)
            ->where('client.mcp_pro_id', $performerProID)
            ->orderBy('appointment.start_time', 'DESC')
            ->get();

        $naApptUpdates = AppointmentConfirmationDecision
            ::select('appointment_confirmation_decision.uid', 'client.name_first', 'client.name_last', 'pro.name_first as pro_name_first', 'pro.name_last as pro_name_last', 'appointment.start_time')
            ->rightJoin('appointment', 'appointment.id', '=', 'appointment_confirmation_decision.appointment_id')
            ->rightJoin('client', 'client.id', '=', 'appointment.client_id')
            ->rightJoin('pro', 'pro.id', '=', 'appointment.pro_id')
            ->where('appointment_confirmation_decision.was_acknowledged_by_client_default_na', false)
            ->where('appointment.status', '!=', 'CREATED')
            ->where('appointment.status', '!=', 'COMPLETED')
            ->where('appointment.status', '!=', 'ABANDONED')
            ->where('client.default_na_pro_id', $performerProID)
            ->orderBy('appointment.start_time', 'DESC')
            ->get();

//        $naApptUpdates = AppointmentConfirmationDecision
//            ::join('appointment', 'appointment.id', '=', 'appointment_confirmation_decision.appointment_id')
//            ->join('client', 'client.id', '=', 'appointment.client_id')
//            ->where('client.default_na_pro_id', $performerProID)
//            ->where('appointment_confirmation_decision.was_acknowledged_by_client_default_na', false)
//            ->orderBy('appointment.start_time DESC')
//            ->get();

        // unstamped client memos
        // for mcp
        $mcpClientMemos = [];
        if($request->input('mcpcm')) {
            $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
            ")
            );
        }
        else {
            $mcpClientMemos = DB::select(
                DB::raw("
SELECT COUNT(cm.id)
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
            ")
            );
        }
        // for na
        $naClientMemos = [];
        if($request->input('nacm')) {
            $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
            ")
            );
        }
        else {
            $naClientMemos = DB::select(
                DB::raw("
SELECT COUNT(cm.id)
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
            ")
            );
        }

        $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;

        if($performer->pro->pro_type === 'ADMIN') {

            // patients without coverage information
            $keyNumbers['patientsWithoutCoverageInformation'] = DB::select(DB::raw("
SELECT count(DISTINCT (cl.id)) as cnt
FROM client cl
WHERE cl.shadow_pro_id IS NULL AND cl.effective_client_primary_coverage_id IS NULL -- no coverage record"
            ))[0]->cnt;

            // patients pending coverage verification
            $keyNumbers['patientsPendingCoverageVerification'] = DB::select(DB::raw("
SELECT count(DISTINCT (cl.id)) as cnt
FROM client cl
         LEFT JOIN client_primary_coverage cpc ON cl.effective_client_primary_coverage_id = cpc.id
WHERE cl.shadow_pro_id IS NULL
    AND (cl.effective_client_primary_coverage_id IS NOT NULL -- coverage exists, but status is null or unknown
    AND (
               (cpc.plan_type = 'MEDICARE' AND (cpc.is_partbprimary = 'UNKNOWN' OR cpc.is_partbprimary IS NULL))
               OR
               (cpc.plan_type != 'MEDICARE' AND
                (cpc.manual_determination_category = 'UNKNOWN' OR cpc.manual_determination_category IS NULL))
           ))"
            ))[0]->cnt;

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

    public function dashboard(Request $request)
    {
        $performer = $this->performer();
        $pro = $performer->pro;
        
        if($pro->pro_type === 'ADMIN'){
            return $this->dashboard_ADMIN($request);
        }elseif($pro->is_enrolled_as_mcp && $pro->is_considered_for_mcp_assignment) {
            return $this->dashboard_MCP($request);
        }elseif($pro->is_hcp){
            return $this->dashboard_HCP($request); //TODO for HCP
        }elseif($pro->is_considered_for_supervising_physician){
            return redirect()->route('ps.dashboard');
        }elseif($pro->is_considered_for_dna){
            return $this->dashboard_DNA($request);
        }elseif($pro->is_considered_for_rd_assignment){
            return redirect()->route('rd.dashboard'); 
        }else{
            return redirect()->route('ca.dashboard'); 
        }
    }


    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') 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
WHERE measurement.label NOT IN ('SBP', 'DBP')
  AND (measurement.is_cellular_zero = FALSE or measurement.is_cellular_zero IS NULL)
  AND measurement.is_active IS TRUE
  AND measurement.has_been_stamped_by_mcp 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 measurement.created_at) = EXTRACT(MONTH from NOW())
  AND EXTRACT(YEAR from measurement.created_at) = 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,
       care_month.start_date as care_month_start_date,
       measurement.label,
       measurement.value,
       measurement.sbp_mm_hg,
       measurement.dbp_mm_hg,
       measurement.numeric_value,
       measurement.value_pulse,
       measurement.value_irregular,
       measurement.ts,
       client.id as client_id,
       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_active IS TRUE
  AND measurement.has_been_stamped_by_mcp 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 measurement.created_at) = EXTRACT(MONTH from NOW())
  AND EXTRACT(YEAR from measurement.created_at) = EXTRACT(YEAR from NOW())
  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');

        $results = $results->leftJoin('client AS c', 'c.id', '=', 'appointment.id');

        if(!$isAdmin) {
            $results = $results->where(function($query) use ($performerProID){
                return $query->where('appointment.pro_id', $performerProID)
                        ->orWhere('c.default_na_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(function($q) use ($performerProID) {
                return $q->where("pro_id", $performerProID)
                    ->orWhereHas('client', function($clientQuery) use ($performerProID){
                        return $clientQuery->where('default_na_pro_id', $performerProID);
                    });
            });
        }

        $appointments = $appointments

	    // ->whereRaw('status NOT IN (\'CANCELLED\', \'COMPLETED\')')
            ->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->clientAge = $appointment->client->age_in_years;
            $appointment->clientSex = $appointment->client->sex;

            $appointment->createdBy = $appointment->creator ? $appointment->creator->displayName() : '';
            $appointment->createdAt = friendly_date($appointment->created_at);

            $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();

            // insurance information
            $appointment->coverage = $appointment->client->getPrimaryCoverageStatus();

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

        return json_encode($appointments);
    }

    public function dashboardAppointmentsDisplay(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(function($q) use ($performerProID) {
                return $q->where("pro_id", $performerProID)
                    ->orWhereHas('client', function($clientQuery) use ($performerProID){
                        return $clientQuery->where('default_na_pro_id', $performerProID);
                    });
            });
        }

        $appointments = $appointments
            ->orderBy('start_time', 'asc')
            ->orderBy('end_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->clientAge = $appointment->client->age_in_years;
            $appointment->clientSex = $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();

            // insurance information
            $appointment->coverage = $appointment->client->getPrimaryCoverageStatus();

            // bg color
            $appointment->bgColor = 'bg-white';
            if($appointment->status === 'COMPLETED') {
                $appointment->bgColor = 'event-bg-green';
            }
            else if($appointment->status === 'CANCELLED') {
                $appointment->bgColor = 'event-bg-gray';
            }

            unset($appointment->client);
            unset($appointment->pro);
            unset($appointment->detail_json);
        }
        return view('app.mcp.dashboard.appointments-list', compact('appointments', 'from', 'to'));
    }

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

    public function patientsSuggest(Request $request)
    {

        $pro = $this->pro;
        $term = $request->input('term') ? trim($request->input('term')) : '';
        $originalTerm = $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]);
        }

        $phoneNumberTerm = preg_replace("/[^0-9]/", "", $originalTerm );
        if($phoneNumberTerm == ""){  //to avoid search with blank string
            $phoneNumberTerm = $term;
        }

        $clientQuery= Client::whereNull('shadow_pro_id');

        //Don't show duplicates
        $clientQuery = $clientQuery->whereNull('duplicate_of_client_id');

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

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

        if(!($pro->pro_type === 'ADMIN' && $pro->can_see_any_client_via_search)) {
            $clientQuery->where(function ($q) use ($pro) {
                if($pro->pro_type === 'ADMIN' || $pro->is_enrolled_as_mcp) {
                    $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 facilitySuggestJSON(Request $request)
    {
        $term = $request->input('term') ? trim($request->input('term')) : '';
        if (empty($term)) return '';

        $terms = explode(' ', $term);
        $terms = array_filter($terms, function($_x) {
            return !!trim($_x);
        });

        $whereCondition = [];
        $whereParams = [];
        for ($i = 0; $i < count($terms); $i++) {
            $whereCondition[] = "(name ILIKE :term{$i} OR address_city ILIKE :term{$i} OR address_state ILIKE :term{$i} OR address_zip ILIKE :term{$i})";
            $whereParams["term{$i}"] = '%' . $terms[$i] . '%';
        }
        $whereCondition = implode(" AND ", $whereCondition);

        $matches = DB::select(
            "SELECT (name || ' ' || address_city || ' ' || address_state || ' ' || address_zip) as text,
       address_line1 as text2,
       name, address_city as city, address_state as state, address_zip as zip, phone, fax FROM facility
       WHERE {$whereCondition} ORDER BY name", $whereParams);
        return json_encode([
            "success" => true,
            "data" => $matches
        ]);
    }

    public function proSuggest(Request $request) {
        $term = $request->input('term') ? trim($request->input('term')) : '';
        if (empty($term)) return '';
        $term = strtolower($term);

        // 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]);
        }

        $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(!empty($term2)) {
            $pros = $pros->where(function ($q) use ($term2) {
                $q->orWhereRaw('LOWER(name_first::text) LIKE ?', ['%' . $term2 . '%'])
                    ->orWhereRaw('LOWER(name_last::text) LIKE ?', ['%' . $term2 . '%'])
                    ->orWhereRaw('cell_number LIKE ?', ['%' . $term2 . '%']);
            });
        }

        $type = $request->input('type') ? trim($request->input('type')) : '';
        if(!!$type) {
            switch(strtolower($type)) {
                case 'hcp':
                    $pros->where('is_hcp', true);
                    break;
                case 'non-hcp':
                    $pros->where('is_hcp', false);
                    break;
                case 'default-na': // TODO: fix condition for NA
                    $pros->where('is_hcp', false)->where('pro_type', '!=', 'ADMIN');
                    break;
                case 'admin':
                    $pros->where('pro_type', 'ADMIN');
                    break;
                case 'non-admin':
                    $pros->where('pro_type', '!=', 'ADMIN');
                    break;
                case 'rmm':
                    $pros->where('is_considered_for_rmm', TRUE);
                    break;
                case 'rme':
                    $pros->where('is_considered_for_rme', TRUE);
                    break;
                case 'rd':
                    $pros->where('is_considered_for_rd_assignment', TRUE);
                    break;
            }
        }

        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;

            // for dna, add pros accessible via pro teams
            if($this->performer->pro->isDefaultNA()) {
                $teams = $this->performer->pro->teamsWhereAssistant;
                foreach ($teams as $team) {
                    if(!in_array($team->mcp_pro_id, $accessibleProIds)) {
                        $accessibleProIds[] = $team->mcp_pro_id;
                    }
                }
            }
                $teams = $this->performer->pro->teamsWhereMcp;
                foreach ($teams as $team) {
                    if(!in_array($team->assistant_pro_id, $accessibleProIds)) {
                        $accessibleProIds[] = $team->assistant_pro_id;
                    }
	        }
            $pros->whereIn('id', $accessibleProIds);
        }
        $suggestedPros = $pros->orderBy('name_last')->orderBy('name_first')->get();

        // for calendar select2
        if($request->input('json')) {
            $onlyProsThatCanAccessClientUid = $request->get('canAccessClientUid');
            $jsonPros = $suggestedPros->map(function($_pro) use($onlyProsThatCanAccessClientUid) {
                if($onlyProsThatCanAccessClientUid){
                    if(!$_pro->canAccess($onlyProsThatCanAccessClientUid)) return false;
                }
                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 canAccessPatient(Request $request, $uid) {
        return json_encode([
            "success" => true,
            "data" => $this->performer->pro->canAccess($uid)
        ]);
    }

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

    public function proUid(Request $request, $id) {
        $pro = Pro::where('id', $id)->first();
        return $pro ? $pro->uid : '';
    }

    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')->paginate(20);
        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'));
        }

        // dummy condition to get the chain-ability going
        $pros =  Pro::where('id', '>', 0);

        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($request->input('redir') ?: '/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);

            $goTo = null;
            if($request->input('redir')){
                $redirUrl = $request->input('redir');
                $possiblePrefixes = ['a', 'm', 'n', 'ca', 'rd'];
                $redirUrlParts = explode('/', $redirUrl);
                $urlPrefix = @$redirUrlParts[1];
                if(in_array($urlPrefix, $possiblePrefixes)){
                    $redirUrlParts[1] = 'a';
                }
                $goTo = implode('/', $redirUrlParts);
            }

            return redirect($goTo ?: 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]);
    }

    public function outgoingEmailTemplates(Request $request){
        $templates = OutgoingEmailTemplate::where('is_active', true)->orderBy('default_subject_tpl', 'ASC')->get();
        return $this->pass($templates);
    }
}