<?php

namespace RxConnexionBundle\Service;

use Doctrine\ORM\EntityManager;
use Symfony\Component\DependencyInjection\ContainerInterface;

Class DashboardService {

    private $connection;
    private $container;
    private $em;

    public function __construct(EntityManager $em, ContainerInterface $container) {
        $this->em = $em;
        $this->connection = $em->getConnection();
        $this->container = $container;
    }

    public function getDashboardData() {
        $session = $this->container->get('session');
        $userData = $session->get('userData');
        if($userData['type'] === "Clinician"){
            return array(
                'appointments' => $this->appointment(),
                'insurance_package' => $this->insurance_package(),
                'appointments_pending_update' => $this->appointmentPendingUpdate(),
                'tickets' => $this->tickets(),
                'reminders' => $this->reminders(),
                'clinician_letters' => $this->clinicianLetters(),
                'clinician_overdue' => $this->encounter_overdues()
            );
        } else {
            return array(
                'withFaxNoEncounters' => $this->withFaxNoEncounters(),
                'withReferralNoEncounters' => $this->withReferralNoEncounters(),
                'newFaxes' => $this->newFaxes(),
                'patientNoCharts' => $this->patientNoCharts(),
                'forRebookings' => $this->forRebookings(),
                'encounterCalls' => $this->encounterCalls(),
                'appointmentNotice' => $this->appointmentNotice(),
                'for72HoursFollowUp' => $this->for72HoursFollowUp(),
                'for3MonthsFollowUp' => $this->for3MonthsFollowUp(),
                'for6MonthsFollowUp' => $this->for6MonthsFollowUp(),
                'myTask' => $this->myTask(),
                'endorsed' => $this->endorsed(),
                'letters' => $this->letters(),
                'accountsReceivable' => $this->accountsReceivable(),
                'appointments' => $this->appointment(),
                'reminder' => $this->reminder(),
                'ppo' => $this->getPPO(),
                'payables' => $this->getPayables(),
            );
        }

    }

    public function getClinicianDashboardData($provider_id) {

        return array(
            'appointments' => $this->clinician_dashboard_appointment($provider_id),
            'insurance_package' => $this->clinician_insurance_package($provider_id),
            'appointments_pending_update' => $this->clinician_dashboard_appointmentPendingUpdate($provider_id),
            'tickets' => $this->clinician_dashboard_tickets($provider_id),
            'reminders' => $this->clinician_dashboard_reminders($provider_id),
            'clinician_letters' => $this->clinician_dashboard_letters($provider_id),
            'encounter_overdue' => $this->clinician_encounter_overdues($provider_id),
        );

    }

    private function withFaxNoEncounters() {

        $sql = $this->connection->prepare("
            SELECT
                COUNT(DISTINCT p.`id`) AS data
            FROM `patient` p
            LEFT JOIN `referral` r ON p.`referral_id` = r.`id`
            LEFT JOIN `fax_tran` f ON r.`fax_tran_id` = f.`id`
            LEFT JOIN (
	              SELECT
		          COUNT(DISTINCT pc.`id`) AS chartCtr,
                  ct.`description` AS chartType_desc,
                  pc.`patient_id`
                FROM `patient_chart` pc
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                GROUP BY pc.`patient_id`
            ) pc ON p.`id` = pc.`patient_id`
            WHERE (p.`id` NOT IN (
              SELECT pc.`patient_id`
              FROM `patient_chart` pc
              LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
              WHERE pc.`patient_id` = p.`id`
              AND pc.`is_deleted` = 0
              GROUP BY pc.`patient_id`
            ) OR pc.`chartCtr` = 1 AND pc.`chartType_desc` = 'Intro Phone Call')
            AND p.`is_active` = 1
            AND r.`fax_tran_id` IS NOT NULL
            AND p.`is_deleted` = 0
            AND p.`id` NOT IN (
               SELECT pt.`patient_id`
               FROM `patient_ticket` pt
               LEFT JOIN `patient` pat2 ON pt.`patient_id` = pat2.`id`
               WHERE pat2.`is_active` = 1
               AND pat2.`is_deleted` = 0
             )
        ");
        $sql->execute();

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;

    }

    private function withReferralNoEncounters() {

        $sql = $this->connection->prepare("
            SELECT
                COUNT(DISTINCT p.`id`) AS data
            FROM `patient` p
            LEFT JOIN `referral` r ON p.`referral_id` = r.`id`
            LEFT JOIN `fax_tran` f ON r.`fax_tran_id` = f.`id`
            LEFT JOIN (
	              SELECT
		          COUNT(DISTINCT pc.`id`) AS chartCtr,
                  ct.`description` AS chartType_desc,
                  pc.`patient_id`
                FROM `patient_chart` pc
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                GROUP BY pc.`patient_id`
            ) pc ON p.`id` = pc.`patient_id`
            WHERE (p.`id` NOT IN (
              SELECT pc.`patient_id`
              FROM `patient_chart` pc
              LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
              WHERE pc.`patient_id` = p.`id`
              AND pc.`is_deleted` = 0
              GROUP BY pc.`patient_id`
            ) OR pc.`chartCtr` = 1 AND pc.`chartType_desc` = 'Intro Phone Call')
            AND p.`is_active` = 1
            AND r.`id` IS NOT NULL
            AND p.`is_deleted` = 0
            AND p.`id` NOT IN (
               SELECT pt.`patient_id`
               FROM `patient_ticket` pt
               LEFT JOIN `patient` pat2 ON pt.`patient_id` = pat2.`id`
               WHERE pat2.`is_active` = 1
               AND pat2.`is_deleted` = 0
             )
        ");
        $sql->execute();

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;

    }

    private function newFaxes() {

        $sql = $this->connection->prepare("
                 SELECT
                  COUNT(DISTINCT ft.`id`) AS data
                FROM `fax_tran` ft
                WHERE ft.`fax_status` = 0
                AND ft.`is_deleted` = 0
        ");
        $sql->execute();

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }

    private function patientNoCharts() {

        $sql = $this->connection->prepare("
            SELECT
                COUNT(DISTINCT p.`id`) AS data
            FROM `patient` p
            WHERE p.`id` NOT IN (
              SELECT pc.`patient_id`
              FROM `patient_chart` pc
              LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
              WHERE (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0)
              AND p.`is_active` = 1
              GROUP BY pc.`patient_id`
            )
            AND p.`is_deleted` = 0
            AND p.`is_active` = 1
            AND p.`id` NOT IN (
               SELECT pt.`patient_id`
               FROM `patient_ticket` pt
               LEFT JOIN `patient` pat2 ON pt.`patient_id` = pat2.`id`
               WHERE pat2.`is_active` = 1
               AND pat2.`is_deleted` = 0
             )
        ");
        $sql->execute();

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }

    private function forRebookings() {

        $sql = $this->connection->prepare("
            SELECT
                COUNT(DISTINCT pc.`id`) AS data
            FROM `patient_chart` pc
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`
            LEFT JOIN (
                  SELECT pct.`id`, pct.`patient_id`, pct.`chart_type_id`, MAX(pct.`chart_date`) AS chart_date
                  FROM `patient_chart` pct 
                  WHERE pct.`is_deleted` = 0
                  GROUP BY pct.`patient_id`, pct.`chart_type_id`
            ) pct ON pc.`patient_id` = pct.`patient_id` AND pc.`chart_type_id` = pct.`chart_type_id` 
            WHERE pc.`chart_date` != '0000-00-00 00:00:00'
            AND (pc.`is_deleted` = '0' OR pc.`is_deleted` IS NULL)
            AND cs.`description` IN ('No Show', 'Cancelled', 'Rescheduled')
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
            AND (pct.`id` IS NULL OR pc.`chart_date` >= pct.`chart_date`)
        ");
        $sql->execute();

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }

    private function encounterCalls() {
        $andWhere = "WHERE pc.`chart_date` != '0000-00-00 00:00:00'
                      AND pc.`patient_id` IS NOT NULL
                      AND pc.`is_deleted` = 0
                      AND p.`is_active` = 1
                      AND s.`schedule_date` >= :currentDate
                      AND ss.`description` != 'Completed'
                      AND IF(pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
                      AND s.`is_deleted` = 0";
//        $leftJoin = "LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
//                  LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
//                  LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`";
        $sql = $this->connection->prepare("
            SELECT
                COUNT(DISTINCT s.`id`) as data
            FROM `schedule` s
            LEFT JOIN `patient` p ON s.`patient_id` = p.`id`
            LEFT JOIN `health_facility` hf ON s.`health_facility_id` = hf.`id`
            LEFT JOIN `patient_chart` pc ON pc.`schedule_id` = s.`id`
            LEFT JOIN `chart_type` ct ON s.`chart_type_id` = ct.`id`
            -- LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN `schedule_status` ss ON s.`schedule_status_id` = ss.`id`
            LEFT JOIN `provider` pr ON s.`provider_id` = pr.`id`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`
            LEFT JOIN `patient_referring_provider` prp ON prp.`id` = (
                    SELECT prp1.`id`
                    FROM `patient_referring_provider` prp1
                    WHERE prp1.`is_deleted` = 0
                    AND prp1.`patient_id` = p.`id`
                    AND prp1.`end_date` IS NULL
                    ORDER BY prp1.`start_date` DESC
                    LIMIT 1
            )
            LEFT JOIN `patient_chart_note` pcn ON pcn.`id` = (
                    SELECT pcn1.`id`
                    FROM `patient_chart_note` pcn1
                    WHERE pcn1.`is_deleted` = 0
                    AND pcn1.`patient_chart_id` = pc.`id`
                    ORDER BY pcn1.`updated_at` DESC
                    LIMIT 1
            )
            LEFT JOIN `referring_provider` rp ON prp.`referring_provider_id` = rp.`id`
            $andWhere
        ");

        $stmtParams['currentDate'] = date('Y-m-d');

        $sql->execute($stmtParams);

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }

    private function appointmentNotice() {

        $sql = $this->connection->prepare("
            SELECT
                COUNT(DISTINCT s.`id`) AS data
            FROM `schedule` s
            LEFT JOIN `chart_type` ct ON s.`chart_type_id` = ct.`id`
            LEFT JOIN `schedule_status` ss ON s.`schedule_status_id` = ss.`id` 
            LEFT JOIN `patient` p ON s.`patient_id` = p.`id` 
            LEFT JOIN `health_facility` hf ON s.`health_facility_id` = hf.`id`
            LEFT JOIN `provider` pr ON s.`provider_id` = pr.`id`
            LEFT JOIN (
                SELECT
                    anf.`schedule_id`,
                    COUNT(DISTINCT ftb.`id`) AS ctr
                FROM `fax_tran_outbound` ftb
                LEFT JOIN `appointment_notice_fax` anf ON ftb.`appointment_notice_fax_id` = anf.`id`
                LEFT JOIN `schedule` s ON anf.`schedule_id` = s.`id`
                LEFT JOIN `patient` p ON s.`patient_id` = p.`id`
                LEFT JOIN `schedule_status` ss ON s.`schedule_status_id` = ss.`id`
                WHERE ftb.`source` = 'SN'
                AND ftb.`status` = 1
                AND s.`schedule_date` != '0000-00-00 00:00:00'
                AND (s.`is_deleted` IS NULL OR s.`is_deleted` = 0)
                AND p.`is_active` = 1
                AND ss.`description` IN(
                  'Booked',
                  'Booked per PT Request'
                )
                GROUP BY anf.`schedule_id`
            ) ftb ON s.`id` = ftb.`schedule_id`
            WHERE s.`schedule_date` != '0000-00-00 00:00:00'
            AND (s.`is_deleted` IS NULL OR s.`is_deleted` = 0)
            AND p.`is_active` = 1
            AND ss.`description` IN(
                  'Booked',
                  'Booked per PT Request'
            )
            AND (ftb.`schedule_id` IS NULL OR ftb.`ctr` <= 0)
            AND ct.`description` IN(
            'HSAT', 
            'HSAT (Mobile)',
            'HSAT Mobile (Repeat)',
            'HSAT Curbside',
            'HSAT Curbside (Repeat)',
            'HSAT Repeat (No Charge)',
            'HSAT Repeat (Paid)'
            )
        ");
        $sql->execute();

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }

    private function for72HoursFollowUp() {

        $sql = $this->connection->prepare("
            SELECT
                COUNT(DISTINCT pc.`id`) AS data 
            FROM `patient_chart` pc
            LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id` 
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id` 
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id` 
            WHERE pc.`chart_date` != '0000-00-00 00:00:00'
            AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0)
            AND DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') BETWEEN DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') AND DATE_FORMAT(DATE_ADD(pc.`chart_date`, INTERVAL 3 DAY), '%m/%d/%Y')  
            AND ct.`description` = 'CPAP Setup'
            AND cs.`description` = 'Completed' 
            AND p.`is_active` = 1
            AND pc.`is_deleted` = 0
            AND IF(ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
        ");
        $sql->execute();

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }

    private function for3MonthsFollowUp() {

        $sql = $this->connection->prepare("
            SELECT
                COUNT(DISTINCT pc.`id`) AS data
            FROM `patient` p
            LEFT JOIN (
                SELECT
                  pc.`patient_id`,
                  MAX(pc.`chart_date`) AS chart_date
                FROM `patient_chart` pc
                LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                WHERE pc.`chart_date` != '0000-00-00 00:00:00'
                AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0)
                AND ct.`description` IN ('CPAP Setup', '3 Month Follow-up')
                AND IF (ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
                AND p.`is_active` = 1
                GROUP BY pc.`patient_id`
            ) pc_max ON p.`id` = pc_max.`patient_id`
            LEFT JOIN `patient_chart` pc ON pc_max.`patient_id` = pc.`patient_id` AND pc_max.`chart_date` = pc.`chart_date` AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0) AND DATE(now()) >= DATE_ADD(DATE_FORMAT(pc.`chart_date`, '%Y-%m-%d'), INTERVAL (3) MONTH)
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`
            WHERE pc.`id` IS NOT NULL
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
        ");
        $sql->execute();

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }

    private function for6MonthsFollowUp() {

        $sql = $this->connection->prepare("
            SELECT
                COUNT(DISTINCT pc.`id`) AS data
            FROM `patient` p
            LEFT JOIN (
                SELECT
                  pc.`patient_id`,
                  MAX(pc.`chart_date`) AS chart_date
                FROM `patient_chart` pc
                LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                WHERE pc.`chart_date` != '0000-00-00 00:00:00'
                AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0)
                AND ct.`description` IN ('CPAP Setup', '6 Month Follow-up')
                AND IF (ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
                AND p.`is_active` = 1
                AND pc.`is_deleted` = 0
                GROUP BY pc.`patient_id`
            ) pc_max ON p.`id` = pc_max.`patient_id`
            LEFT JOIN `patient_chart` pc ON pc_max.`patient_id` = pc.`patient_id` AND pc_max.`chart_date` = pc.`chart_date` AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0) AND DATE(now()) >= DATE_ADD(DATE_FORMAT(pc.`chart_date`, '%Y-%m-%d'), INTERVAL (6) MONTH)
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`
            WHERE pc.`id` IS NOT NULL
            AND p.`is_active` = 1
            AND ct.`description` IN ('CPAP Setup', '6 Month Follow-up')
            AND IF (ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
            AND p.`is_deleted` = 0
        ");
        $sql->execute();

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }

    private function myTask() {
        $andWhere = '';
        $andWhere2 = '';
        $stmtParams = array();
        $stmtParams2 = array();
        if(isset($_COOKIE['userId']) && $_COOKIE['userType'] != 'Super Admin'){
            $andWhere .= " AND pc.`account_user_moa_assignment_id` = :userId";
            $stmtParams['userId'] = $_COOKIE['userId'];
        }

        if(isset($_COOKIE['userId']) && $_COOKIE['userType'] != 'Super Admin'){
            $andWhere2 .= " AND (t.`account_user_id` = :userId OR tcc.`account_user_id` = :userId)";
            $stmtParams2['userId'] = $_COOKIE['userId'];
        }
        $sql = $this->connection->prepare("
            SELECT
                COUNT(DISTINCT pc.`id`) AS data
            FROM `patient_chart` pc
            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
            LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN (
                  SELECT pcn.`id`, pcn.`patient_chart_id`, MAX(pcn.`created_at`) AS created_at
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  GROUP BY pcn.`patient_chart_id`
             ) pcnt ON pcnt.`patient_chart_id` = pc.`id`
             LEFT JOIN (
                  SELECT pcn.`note`, pcn.`patient_chart_id`, pcn.`created_at`
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  GROUP BY pcn.`patient_chart_id`, pcn.`created_at`
             ) pcn ON pcn.`patient_chart_id` = pc.`id` AND pcnt.`created_at` = pcn.`created_at`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`
            WHERE pc.`is_deleted` = 0
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
            AND pc.`patient_id` IS NOT NULL
            AND cs.`description` != 'Deleted'
            $andWhere
        ");
        $sql->execute($stmtParams);

        $sql2 = $this->connection->prepare(
            "
            SELECT
                COUNT(DISTINCT t.`id`) AS data
            FROM `ticket` t
            LEFT JOIN `ticket_status` ts ON t.`ticket_status_id` = ts.`id`
            LEFT JOIN `ticket_cc` tcc ON tcc.`ticket_id` = t.`id`
            WHERE t.`is_deleted` = 0
            AND ts.`description` != 'Closed'
            $andWhere2
        "
        );
        $sql2->execute($stmtParams2);
        $sqlCount = $sql->rowCount() ?  $sql->fetch(0)['data'] : 0;
        $sqlCount2 = $sql2->rowCount() ?  $sql2->fetch(0)['data'] : 0;
        return $sqlCount + $sqlCount2;
    }

    private function endorsed() {
        $sql = $this->connection->prepare("
            SELECT
                COUNT(DISTINCT t.`id`) AS data
            FROM `ticket` t
            LEFT JOIN `ticket_status` ts ON t.`ticket_status_id` = ts.`id`
            WHERE t.`is_deleted` = 0
            AND ts.`description` != 'Closed'
        ");
        $sql->execute();

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }

    private function letters() {

        $andWhere = 'WHERE pl.`is_deleted` = 0';
        $andWhere .= " AND pls.`description` != 'Sent'";
        $stmtParams = array();
        if($_COOKIE['username'] != 'superadmin') {
            if(isset($_COOKIE['username']) && $_COOKIE['username'] != '') {
                $andWhere .= ' AND au.`username` = :username';
                $stmtParams['username'] = $_COOKIE['username'];
            }
        }


        $sql = $this->connection->prepare("
            SELECT
                COUNT(DISTINCT pl.`id`) AS data
            FROM `patient_letter` pl
            LEFT JOIN `account_user` au ON pl.`account_user_id` = au.`id`
            LEFT JOIN `patient_letter_status` pls ON pl.`patient_letter_status_id` = pls.`id`
            $andWhere
        ");
        $sql->execute($stmtParams);

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }

    private function accountsReceivable() {

        $sql = $this->connection->prepare("
            SELECT
              COUNT(pb.`id`) AS data
            FROM `patient_billing` pb
            LEFT JOIN `patient` pat ON pb.`patient_id` = pat.`id`
            LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`
            LEFT JOIN `company` hf_c ON hf.`company_id` = hf_c.`id`
            LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`
            LEFT JOIN `company` hf2_c ON hf2.`company_id` = hf2_c.`id`
            LEFT JOIN `health_facility` hf3 ON hf3.id = IF(pb.`health_facility_for_address_id` IS NULL, pb.`health_facility_id`, pb.`health_facility_for_address_id`)
            LEFT JOIN company hf3_c on hf3.`company_id` = hf3_c.`id`
            LEFT JOIN `patient_billing_financing_payment_detail` pbfpd ON pbfpd.`patient_billing_id` = pb.`id`
            LEFT JOIN(
              SELECT
                pins.`id` patientInsuranceId,
                i.`description` patientInsurance
              FROM `patient_insurance` pins
              LEFT JOIN `insurance` i ON pins.`insurance_id` = i.`id`
            ) tmp_ins ON tmp_ins.`patientInsuranceId` = pb.`patient_insurance_id`
            LEFT JOIN `patient_payment` pp ON pp.`id` = (
              SELECT
                pp2.`id`
              FROM `patient_payment` pp2
              WHERE pp2.`patient_id` = pat.`id`
              AND pp2.`patient_billing_id` = pb.`id`
              ORDER BY pp2.`pay_date` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pc ON pc.`id` = (
              SELECT pc2.`id`
              FROM `patient_chart` pc2
              LEFT JOIN `chart_type` ct ON pc2.`chart_type_id` = ct.`id`
              WHERE ct.`description` IN('Phone Call - AR (CA)', 'Phone Call - AR (CBT)')
              AND pc2.`patient_id` = pat.`id`
              ORDER BY pc2.`chart_date` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_payment` pp_f ON pb.`patient_payment_id_financing` = pp_f.`id`
            LEFT JOIN `financing_company` fc ON pp_f.`financing_company_id` = fc.`id`
            WHERE pb.`is_deleted` = 0
            AND pb.`amount_due` != 0
            AND pat.`is_test_patient` != 1
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf2.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND pb.`billing_status` = 'Pending Payment'
            ORDER BY pat.`full_name` ASC
        ");
        $sql->execute();

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }

    private function reminder() {

        $session = $this->container->get('session');
        $userData = $session->get('userData');

        $accountUser = $this->em->getRepository('RxConnexionBundle:AccountUserEntity')->find($userData['id']);

        $sql = $this->connection->prepare("
            SELECT
              COUNT(DISTINCT aur.id) AS data
            FROM `account_user_reminder` aur 
            LEFT JOIN `reminder_type` rt ON aur.`reminder_type_id` = rt.`id`
            LEFT JOIN `account_user` assigned_au ON aur.`assigned_to_account_user_id` = assigned_au.`id`
            LEFT JOIN `account_user` created_au ON aur.`account_user_id` = created_au.`id`
            LEFT JOIN `user_group` ug ON aur.`assigned_to_user_group_id` = ug.`id` 
            WHERE (aur.`account_user_id` = :user_id)
            OR aur.`assigned_to_account_user_id` = :user_id
            OR aur.`assigned_to_user_group_id` IN (
                SELECT ugau.`user_group_id`
                FROM `user_group_account_user` ugau
                WHERE ugau.`account_user_id` = :user_id
            )
        ");
        $params = array(
            'user_id' => $accountUser->getId()
        );
        $sql->execute($params);

        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }

    public function appointment() {
        $session = $this->container->get('session');
        $userData = $session->get('userData');

        $accountUser = $this->em->getRepository('RxConnexionBundle:AccountUserEntity')->find($userData['id']);

        $sql = $this->connection->prepare("
            SELECT 
              CONCAT_WS(' ', s.`start_time`, pat.`full_name`, CONCAT('(', ct.`description`, ')') , ss.`description`) as displayData,
              STR_TO_DATE(s.`start_time`, '%l:%i %p') as dd,
              s.`id`,
              s.`start_time`,
              s.`end_time`,
              pat.`full_name` as patient_fullName, 
              p.`full_name` as provider_fullName, 
              ss.`description` as schedule_status, 
              DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y') AS scheduleDate,
              CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), s.`start_time`) AS scheduleDT,
              CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), s.`end_time`) AS scheduleDTEnd,
              ct.`description` AS chartType,
              ct.`color`
            FROM `schedule` s
            LEFT JOIN `patient` pat ON s.`patient_id` = pat.`id`
            LEFT JOIN `health_facility` hf ON s.`health_facility_id` = hf.`id`
            LEFT JOIN `chart_type` ct on s.`chart_type_id` = ct.`id`
            LEFT JOIN `schedule_status` ss on s.`schedule_status_id` = ss.`id`
            LEFT JOIN `provider` p on s.`provider_id` = p.`id`
            WHERE s.`is_deleted` = 0
            AND s.`schedule_date` = :now
            AND s.`provider_id` = :provider_id
            ORDER BY dd ASC
        ");
        $params = array(
            'now' => date('Y-m-d 00:00:00'),
            'provider_id' => $accountUser->getProvider() ? $accountUser->getProvider()->getId() : null,
        );
        $sql->execute($params);

        return $sql->fetchAll();
    }

    public function clinician_dashboard_appointment($provider_id) {
        $session = $this->container->get('session');
        $userData = $session->get('userData');

        $accountUser = $this->em->getRepository('RxConnexionBundle:AccountUserEntity')->find($userData['id']);

        $sql = $this->connection->prepare("
            SELECT 
              CONCAT_WS(' ', s.`start_time`, pat.`full_name`, CONCAT('(', ct.`description`, ')') , ss.`description`) as displayData,
              STR_TO_DATE(s.`start_time`, '%l:%i %p') as dd,
              s.`id`,
              s.`start_time`,
              s.`end_time`,
              pat.`full_name` as patient_fullName, 
              p.`full_name` as provider_fullName, 
              ss.`description` as schedule_status, 
              DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y') AS scheduleDate,
              CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), s.`start_time`) AS scheduleDT,
              CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), s.`end_time`) AS scheduleDTEnd,
              ct.`description` AS chartType,
              ct.`color`
            FROM `schedule` s
            LEFT JOIN `patient` pat ON s.`patient_id` = pat.`id`
            LEFT JOIN `health_facility` hf ON s.`health_facility_id` = hf.`id`
            LEFT JOIN `chart_type` ct on s.`chart_type_id` = ct.`id`
            LEFT JOIN `schedule_status` ss on s.`schedule_status_id` = ss.`id`
            LEFT JOIN `provider` p on s.`provider_id` = p.`id`
            WHERE s.`is_deleted` = 0
            AND s.`schedule_date` = :now
            AND s.`provider_id` = :provider_id
            ORDER BY dd ASC
        ");
        $params = array(
            'now' => date('Y-m-d 00:00:00'),
            'provider_id' => $provider_id,
        );
        $sql->execute($params);

        return $sql->fetchAll();
    }

    public function appointmentPendingUpdate() {
        $session = $this->container->get('session');
        $userData = $session->get('userData');

        $accountUser = $this->em->getRepository('RxConnexionBundle:AccountUserEntity')->find($userData['id']);

        $sql = $this->connection->prepare("
            SELECT 
              CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), pat.`full_name`, CONCAT('(', ct.`description`, ')') , ss.`description`) as displayData,
              STR_TO_DATE(s.`start_time`, '%l:%i %p') as dd,
              s.`id`,
              s.`start_time`,
              s.`end_time`,
              pat.`full_name` as patient_fullName, 
              p.`full_name` as provider_fullName, 
              ss.`description` as schedule_status, 
              ss.`description` as schedule_status, 
              DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y') AS displaySched,
              CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), s.`start_time`) AS scheduleDT,
              CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), s.`end_time`) AS scheduleDTEnd,
              ct.`description` AS chartType,
              ct.`color`
            FROM `schedule` s
            LEFT JOIN `patient` pat ON s.`patient_id` = pat.`id`
            LEFT JOIN `health_facility` hf ON s.`health_facility_id` = hf.`id`
            LEFT JOIN `chart_type` ct on s.`chart_type_id` = ct.`id`
            LEFT JOIN `schedule_status` ss on s.`schedule_status_id` = ss.`id`
            LEFT JOIN `provider` p on s.`provider_id` = p.`id`
            WHERE s.`is_deleted` = 0
            AND s.`provider_id` = :provider_id
            AND (s.`schedule_date` < :now AND ss.`description` IN ('New', 'Booked', 'Pending', 'Scheduled'))
            ORDER BY s.schedule_date DESC, dd ASC
        ");
        $params = array(
            'now' => date('Y-m-d 00:00:00'),
            'provider_id' => $accountUser->getProvider() ? $accountUser->getProvider()->getId() : '',
        );
        $sql->execute($params);

        return $sql->fetchAll();
    }

    public function clinician_dashboard_appointmentPendingUpdate($provider_id) {
        $session = $this->container->get('session');
        $userData = $session->get('userData');

        $accountUser = $this->em->getRepository('RxConnexionBundle:AccountUserEntity')->find($userData['id']);

        $sql = $this->connection->prepare("
            SELECT 
              CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), pat.`full_name`, CONCAT('(', ct.`description`, ')') , ss.`description`) as displayData,
              STR_TO_DATE(s.`start_time`, '%l:%i %p') as dd,
              s.`id`,
              s.`start_time`,
              s.`end_time`,
              pat.`full_name` as patient_fullName, 
              p.`full_name` as provider_fullName, 
              ss.`description` as schedule_status, 
              ss.`description` as schedule_status, 
              DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y') AS displaySched,
              CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), s.`start_time`) AS scheduleDT,
              CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), s.`end_time`) AS scheduleDTEnd,
              ct.`description` AS chartType,
              ct.`color`
            FROM `schedule` s
            LEFT JOIN `patient` pat ON s.`patient_id` = pat.`id`
            LEFT JOIN `health_facility` hf ON s.`health_facility_id` = hf.`id`
            LEFT JOIN `chart_type` ct on s.`chart_type_id` = ct.`id`
            LEFT JOIN `schedule_status` ss on s.`schedule_status_id` = ss.`id`
            LEFT JOIN `provider` p on s.`provider_id` = p.`id`
            WHERE s.`is_deleted` = 0
            AND s.`provider_id` = :provider_id
            AND (s.`schedule_date` < :now AND ss.`description` IN ('New', 'Booked', 'Pending', 'Scheduled'))
            ORDER BY s.schedule_date DESC, dd ASC
        ");
        $params = array(
            'now' => date('Y-m-d 00:00:00'),
            'provider_id' => $provider_id,
        );
        $sql->execute($params);

        return $sql->fetchAll();
    }

    private function tickets() {
        $session = $this->container->get('session');
        $userData = $session->get('userData');

        $sql = $this->connection->prepare("
            SELECT
                TO_BASE64(t.`id`) as idEncoded,
                t.`id`,
                ts.`description` as status,
                s.`description` as severity,
                CONCAT('[ ' , DATE_FORMAT(tn.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', tn.`created_by`, ' ] : ', tn.`note`) AS latestNote
            FROM `ticket` t
            LEFT JOIN `ticket_cc` tcc ON tcc.`ticket_id` = t.`id`
            LEFT JOIN `ticket_status` ts ON t.`ticket_status_id` = ts.`id`
            LEFT JOIN `ticket_severity` s ON t.`ticket_severity_id` = s.`id`
            LEFT JOIN `account_user` au ON t.`account_user_id` = au.`id`
            LEFT JOIN `ticket_note` tn ON tn.`id` = (
                SELECT tnt.`id`
                FROM `ticket_note` tnt
                WHERE tnt.`ticket_id` = t.`id`
                ORDER BY tnt.`created_at` DESC
                LIMIT 1
            )
            WHERE (t.`account_user_id` = :user_id OR tcc.`account_user_id` = :user_id)
            AND t.`is_deleted` = 0
            AND ts.`description` != 'Closed'
            GROUP BY t.`id`
            ORDER BY t.`created_at`  DESC
        ");
        $params = array(
            'user_id' => $userData['id']
        );
        $sql->execute($params);

        return $sql->fetchAll();
    }

    private function encounter_overdues() {
        $session = $this->container->get('session');
        $userData = $session->get('userData');
        $sqlWhere = '';

        if (!is_null($userData) && !empty($userData)) {
            if ($userData['type'] != 'Super Admin') {
                if (count($userData['company']['companyBranches']) > 0) {
                    $hf_ors = '';
                    foreach ($userData['company']['companyBranches'] as $key => $branchId) {
                        $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "phf.`health_facility_id` = $branchId";
                    }
                    $sqlWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors) ";
                } else {
                    $sqlWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
                }
            }
        }

        $sql1 = $this->connection->prepare("
            SELECT
                TO_BASE64(pc.`patient_id`) AS patientId,
                TO_BASE64(pc.`id`) AS patientChartId,
                p.`full_name` AS patientName,
                ct.`description` AS chartType,
               CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_ADD(:now, 
                    INTERVAL (
                    SELECT 
                        ct_sub.`expected_no_of_days`
                    FROM `chart_type` ct_sub
                    WHERE ct_sub.`description` = 'HSAT Results Interpretation' 
                        AND pc.`chart_type_id` = ct_sub.`id`
                        LIMIT 1
                ) DAY)), ' Day(s)'
               ) AS dayOverDue 
            FROM `patient_chart` pc
            LEFT JOIN `patient` p ON p.`id` = pc.`patient_id`
            LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
            LEFT JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
            WHERE pc.`is_deleted` = 0
            AND ct.`description` = 'HSAT Results Interpretation'
            AND cs.`description` != 'Completed'
            AND p.`referral_date` IS NOT NULL
            AND IF (
                pc.`id` IS NOT NULL,
                DATEDIFF(pc.`chart_date`, p.`referral_date`) > ct.`expected_no_of_days`,
               DATE_ADD(p.`referral_date`, INTERVAL (
                SELECT 
                    ct_sub.`expected_no_of_days`
                     FROM `chart_type` ct_sub
                     WHERE ct_sub.`description` = 'CPAP Setup' 
                     LIMIT 1
               ) DAY) < :now
            )
            AND pc.`provider_id` = :provider_id
        ");

        $sql2 = $this->connection->prepare("
            SELECT
                TO_BASE64(pc.`patient_id`) AS patientId,
                TO_BASE64(pc.`id`) AS patientChartId,
                p.`full_name` AS patientName,
                ct.`description` AS chartType,
               CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_ADD(:now, 
                    INTERVAL (
                    SELECT 
                        ct_sub.`expected_no_of_days`
                    FROM `chart_type` ct_sub
                    WHERE ct_sub.`description` = 'CPAP Setup' 
                        AND pc.`chart_type_id` = ct_sub.`id`
                        LIMIT 1
                ) DAY)), ' Day(s)'
               ) AS dayOverDue 
            FROM `patient_chart` pc
            LEFT JOIN `patient` p ON p.`id` = pc.`patient_id`
            LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
            LEFT JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
            WHERE pc.`is_deleted` = 0
            AND ct.`description` = 'CPAP Setup'
            AND cs.`description` != 'Completed'
            AND p.`referral_date` IS NOT NULL
            AND IF (
                pc.`id` IS NOT NULL,
                DATEDIFF(pc.`chart_date`, p.`referral_date`) > ct.`expected_no_of_days`,
               DATE_ADD(p.`referral_date`, INTERVAL (
                SELECT 
                    ct_sub.`expected_no_of_days`
                     FROM `chart_type` ct_sub
                     WHERE ct_sub.`description` = 'CPAP Setup Sold' 
                     LIMIT 1
               ) DAY) < :now
            )
            AND pc.`provider_id` = :provider_id
        ");

        $sql3 = $this->connection->prepare("
            SELECT
                TO_BASE64(pc.`patient_id`) AS patientId,
                TO_BASE64(pc.`id`) AS patientChartId,
                p.`full_name` AS patientName,
                ct.`description` AS chartType,
               CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_ADD(:now, 
                    INTERVAL (
                    SELECT 
                        ct_sub.`expected_no_of_days`
                    FROM `chart_type` ct_sub
                    WHERE ct_sub.`description` = 'CPAP Setup Sold' 
                        AND pc.`chart_type_id` = ct_sub.`id`
                        LIMIT 1
                ) DAY)), ' Day(s)'
               ) AS dayOverDue 
            FROM `patient_chart` pc
            LEFT JOIN `patient` p ON p.`id` = pc.`patient_id`
            LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
            LEFT JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
            WHERE pc.`is_deleted` = 0
            AND ct.`description` = 'CPAP Setup Sold'
            AND cs.`description` != 'Completed'
            AND p.`referral_date` IS NOT NULL
            AND IF (
                pc.`id` IS NOT NULL,
                DATEDIFF(pc.`chart_date`, p.`referral_date`) > ct.`expected_no_of_days`,
               DATE_ADD(p.`referral_date`, INTERVAL (
                SELECT 
                    ct_sub.`expected_no_of_days`
                     FROM `chart_type` ct_sub
                     WHERE ct_sub.`description` = 'CPAP Eval' 
                     LIMIT 1
               ) DAY) < :now
            )
            AND pc.`provider_id` = :provider_id
        ");

        $sql4 = $this->connection->prepare("
            SELECT
                TO_BASE64(pc.`patient_id`) AS patientId,
                TO_BASE64(pc.`id`) AS patientChartId,
                p.`full_name` AS patientName,
                ct.`description` AS chartType,
               CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_ADD(:now, 
                    INTERVAL (
                    SELECT 
                        ct_sub.`expected_no_of_days`
                    FROM `chart_type` ct_sub
                    WHERE ct_sub.`description` = 'CPAP Eval' 
                        AND pc.`chart_type_id` = ct_sub.`id`
                        LIMIT 1
                ) DAY)), ' Day(s)'
               ) AS dayOverDue 
            FROM `patient_chart` pc
            LEFT JOIN `patient` p ON p.`id` = pc.`patient_id`
            LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
            LEFT JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
            WHERE pc.`is_deleted` = 0
            AND ct.`description` = 'CPAP Eval'
            AND cs.`description` != 'Completed'
            AND p.`referral_date` IS NOT NULL
            AND IF (
                pc.`id` IS NOT NULL,
                DATEDIFF(pc.`chart_date`, p.`referral_date`) > ct.`expected_no_of_days`,
               DATE_ADD(p.`referral_date`, INTERVAL (
                SELECT 
                    ct_sub.`expected_no_of_days`
                     FROM `chart_type` ct_sub
                     WHERE ct_sub.`description` = 'CPAP End Eval' 
                     LIMIT 1
               ) DAY) < :now
            )
            AND pc.`provider_id` = :provider_id
        ");

        $sql5 = $this->connection->prepare("
            SELECT
                TO_BASE64(pc.`patient_id`) AS patientId,
                TO_BASE64(pc.`id`) AS patientChartId,
                p.`full_name` AS patientName,
                ct.`description` AS chartType,
               CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_ADD(:now, 
                    INTERVAL (
                    SELECT 
                        ct_sub.`expected_no_of_days`
                    FROM `chart_type` ct_sub
                    WHERE ct_sub.`description` = 'CPAP End Eval' 
                        AND pc.`chart_type_id` = ct_sub.`id`
                        LIMIT 1
                ) DAY)), ' Day(s)'
               ) AS dayOverDue 
            FROM `patient_chart` pc
            LEFT JOIN `patient` p ON p.`id` = pc.`patient_id`
            LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
            LEFT JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
            WHERE pc.`is_deleted` = 0
            AND ct.`description` = 'CPAP End Eval'
            AND cs.`description` != 'Completed'
            AND p.`referral_date` IS NOT NULL
            AND pc.`provider_id` = :provider_id
        ");
        $params = array(
            'provider_id' => base64_decode($userData['providerId']),
            'now' => date('Y-m-d 00:00:00')
        );

        $sql1->execute($params);
        $sql2->execute($params);
        $sql3->execute($params);
        $sql4->execute($params);
        $sql5->execute($params);

        $result['hri'] = $sql1->fetchAll();
        $result['cs'] = $sql2->fetchAll();
        $result['css'] = $sql3->fetchAll();
        $result['ce'] = $sql4->fetchAll();
        $result['cee'] = $sql5->fetchAll();

        return $result;
    }

    private function insurance_package() {
        $session = $this->container->get('session');
        $userData = $session->get('userData');

        $sysParam =  $this->em->getRepository('RxConnexionBundle:SystemParameterEntity')->findOneBy(array(
           'description' => 'Insurance Package'
        ));

        $insurancePackageDate = '';
        if(isset($sysParam) && $sysParam != 'null') {
            $insurancePackageDate = $sysParam->getValue();
        } else {
            $insurancePackageDate = date('Y-m-d');
        }

        $sql = $this->connection->prepare("
            SELECT 
                TO_BASE64(pb.`patient_id`) as patientIdEncoded,
                IF(pb.`invoice_no` IS NOT NULL, pb.`invoice_no`, pb.`prev_sys_invoice_no`) invoiceNo,
                p.`full_name` AS patientName,
                DATE_FORMAT(pb.`billing_date`, '%m/%d/%Y') AS billingDate
            FROM `patient_billing` pb
            LEFT JOIN `patient` p ON pb.`patient_id` = p.`id`
            LEFT JOIN `patient_chart` pc ON pc.`id` = (
                SELECT pc1.`id`
                FROM `patient_chart` pc1
                LEFT JOIN `chart_type` ct1 ON pc1.`chart_type_id` = ct1.`id`
                WHERE pc1.`is_deleted` = 0
                AND pb.`patient_id` = pc1.`patient_id`
                AND ct1.`description` = 'HSAT'
                ORDER BY pc1.`chart_date` ASC
                LIMIT 1
            )
            LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN `account_user` au ON pc.`provider_id` = au.`provider_id`
            LEFT JOIN `patient_file` pf ON pb.`patient_file_id` = pf.`id`
            WHERE pb.`is_deleted` = 0
            AND pb.`payer_type` = 'Insurance'
            AND pb.`created_at` BETWEEN '2020-1-1' AND :systemParamDate
            AND pb.`patient_file_id` IS NULL
            AND au.`id` = :user_id
        ");
        $params = array(
            'user_id' => $userData['id'],
//            'now' => date('Y-m-d 00:00:00')
            'systemParamDate' => $insurancePackageDate
        );
        $sql->execute($params);

        return $sql->fetchAll();
    }

    private function clinicianLetters() {
        $session = $this->container->get('session');
        $userData = $session->get('userData');

        $sql = $this->connection->prepare("
            SELECT 
                TO_BASE64(pl.`id`) AS idEncoded,
                pl.`letter_type`, 
                pl.`recipient`, 
                DATE_FORMAT(pl.`created_at`, '%m/%d/%Y') AS createdAt,
                TO_BASE64(pl.`patient_id`) AS patientIdEncoded,
                pl.`id`
            FROM `patient_letter` pl
            LEFT JOIN `patient` p ON pl.`patient_id` = p.`id`
            LEFT JOIN `account_user` au ON pl.`account_user_id` = au.`id`
            LEFT JOIN `patient_letter_status` pls ON pl.`patient_letter_status_id` = pls.`id`
            WHERE pl.`is_deleted` = 0
            AND pls.`description` != 'Sent'
            AND pl.`account_user_id` = :user_id
            ORDER BY pl.`created_at` DESC
        ");
        $params = array(
            'user_id' => $userData['id']
        );
        $sql->execute($params);

        return $sql->fetchAll();
    }

    private function clinician_dashboard_tickets($provider_id) {
        $session = $this->container->get('session');
        $userData = $session->get('userData');

        $sql = $this->connection->prepare("
            SELECT
                TO_BASE64(t.`id`) as idEncoded,
                t.`id`,
                ts.`description` as status,
                s.`description` as severity,
                CONCAT('[ ' , DATE_FORMAT(tn.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', tn.`created_by`, ' ] : ', tn.`note`) AS latestNote
            FROM `ticket` t
            LEFT JOIN `ticket_cc` tcc ON tcc.`ticket_id` = t.`id`
            LEFT JOIN `ticket_status` ts ON t.`ticket_status_id` = ts.`id`
            LEFT JOIN `ticket_severity` s ON t.`ticket_severity_id` = s.`id`
            LEFT JOIN `account_user` au ON t.`account_user_id` = au.`id`
            LEFT JOIN `account_user` au1 ON tcc.`account_user_id` = au1.`id`
            LEFT JOIN `provider` pr ON au.`provider_id` = pr.`id`
            LEFT JOIN `provider` pr1 ON au1.`provider_id` = pr1.`id`
            LEFT JOIN `ticket_note` tn ON tn.`id` = (
                SELECT tnt.`id`
                FROM `ticket_note` tnt
                WHERE tnt.`ticket_id` = t.`id`
                ORDER BY tnt.`created_at` DESC
                LIMIT 1
            )
            WHERE (au.`provider_id` = :user_id OR pr1.`id` = :user_id)
            AND t.`is_deleted` = 0
            AND ts.`description` != 'Closed'
            GROUP BY t.`id`
            ORDER BY t.`created_at`  DESC
        ");
        $params = array(
            'user_id' => $provider_id
        );
        $sql->execute($params);

        return $sql->fetchAll();
    }

    private function reminders() {
        $session = $this->container->get('session');
        $userData = $session->get('userData');
        return $this->em->getRepository('RxConnexionBundle:AccountUserReminderEntity')->findForUser($userData);

        /*$sql = $this->connection->prepare("
            SELECT
              rt.`description` as reminderType,
              aur.`note`,
              aur.`id`,
              DATE_FORMAT(aur.`created_at`, '%m/%d/%Y') as createdAt
            FROM `account_user_reminder` aur
            LEFT JOIN `reminder_type` rt ON aur.`reminder_type_id` = rt.`id`
            WHERE aur.`account_user_id` = :user_id
             OR aur.`assigned_to_account_user_id` = :user_id
             OR aur.`assigned_to_user_group_id` IN (
              SELECT ugau.`user_group_id`
              FROM `user_group_account_user` ugau
              WHERE ugau.`account_user_id` = aur.`account_user_id`
             )
            AND aur.`status` != 'Completed'
            ORDER BY aur.`id`  DESC
        ");
        $params = array(
            'user_id' => $userData['id']
        );
        $sql->execute($params);*/

        return $sql->fetchAll();
    }

    private function clinician_dashboard_reminders($provider_id) {
//        $session = $this->container->get('session');
//        $userData = $session->get('userData');
        return $this->em->getRepository('RxConnexionBundle:AccountUserReminderEntity')->clinician_dashboardFindForUser($provider_id);

        /*$sql = $this->connection->prepare("
            SELECT
              rt.`description` as reminderType,
              aur.`note`,
              aur.`id`,
              DATE_FORMAT(aur.`created_at`, '%m/%d/%Y') as createdAt
            FROM `account_user_reminder` aur
            LEFT JOIN `reminder_type` rt ON aur.`reminder_type_id` = rt.`id`
            WHERE aur.`account_user_id` = :user_id
             OR aur.`assigned_to_account_user_id` = :user_id
             OR aur.`assigned_to_user_group_id` IN (
              SELECT ugau.`user_group_id`
              FROM `user_group_account_user` ugau
              WHERE ugau.`account_user_id` = aur.`account_user_id`
             )
            AND aur.`status` != 'Completed'
            ORDER BY aur.`id`  DESC
        ");
        $params = array(
            'user_id' => $userData['id']
        );
        $sql->execute($params);*/

//        return $sql->fetchAll();
    }

    private function clinician_dashboard_letters($provider_id) {
        $session = $this->container->get('session');
        $userData = $session->get('userData');

        $user = $this->em->getRepository('RxConnexionBundle:AccountUserEntity')->findOneBy(array(
           'provider' => $provider_id,
        ));

        $sql = $this->connection->prepare("
            SELECT 
                TO_BASE64(pl.`id`) AS idEncoded,
                pl.`letter_type`, 
                pl.`recipient`, 
                DATE_FORMAT(pl.`created_at`, '%m/%d/%Y') AS createdAt,
                TO_BASE64(pl.`patient_id`) AS patientIdEncoded,
                pl.`id`
            FROM `patient_letter` pl
            LEFT JOIN `patient` p ON pl.`patient_id` = p.`id`
            LEFT JOIN `account_user` au ON pl.`account_user_id` = au.`id`
            LEFT JOIN `patient_letter_status` pls ON pl.`patient_letter_status_id` = pls.`id`
            WHERE pl.`is_deleted` = 0
            AND pls.`description` != 'Sent'
            AND pl.`account_user_id` = :user_id
            ORDER BY pl.`created_at` DESC
        ");

        $userId = null;
        if (isset($user)) {
            $userId =  $user->getId();
        }

        $params = array(
            'user_id' => $userId
        );
        $sql->execute($params);

        return $sql->fetchAll();
    }

    private function clinician_insurance_package($provider_id) {
        $session = $this->container->get('session');
        $userData = $session->get('userData');

        $sysParam =  $this->em->getRepository('RxConnexionBundle:SystemParameterEntity')->findOneBy(array(
            'description' => 'Insurance Package'
        ));

        $insurancePackageDate = '';
        if(isset($sysParam) && $sysParam != 'null') {
            $insurancePackageDate = $sysParam->getValue();
        } else {
            $insurancePackageDate = date('Y-m-d');
        }

        $sql = $this->connection->prepare("
            SELECT 
                TO_BASE64(pb.`patient_id`) as patientIdEncoded,
                IF(pb.`invoice_no` IS NOT NULL, pb.`invoice_no`, pb.`prev_sys_invoice_no`) invoiceNo,
                p.`full_name` AS patientName,
                DATE_FORMAT(pb.`billing_date`, '%m/%d/%Y') AS billingDate
            FROM `patient_billing` pb
            LEFT JOIN `patient` p ON pb.`patient_id` = p.`id`
            LEFT JOIN `patient_chart` pc ON pc.`id` = (
                SELECT pc1.`id`
                FROM `patient_chart` pc1
                LEFT JOIN `chart_type` ct1 ON pc1.`chart_type_id` = ct1.`id`
                WHERE pc1.`is_deleted` = 0
                AND pb.`patient_id` = pc1.`patient_id`
                AND ct1.`description` = 'HSAT'
                ORDER BY pc1.`chart_date` ASC
                LIMIT 1
            )
            LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN `patient_file` pf ON pb.`patient_file_id` = pf.`id`
            WHERE pb.`is_deleted` = 0
            AND pb.`created_at` BETWEEN '2020-1-1' AND :systemParamDate
            AND pb.`payer_type` = 'Insurance'
            AND pb.`patient_file_id` IS NULL
            AND pc.`provider_id` = :provider_id
        ");
        $params = array(
            'provider_id' => $provider_id,
            'systemParamDate' => $insurancePackageDate
        );
        $sql->execute($params);

        return $sql->fetchAll();
    }

    private function clinician_encounter_overdues($provider_id) {
        $session = $this->container->get('session');
        $userData = $session->get('userData');

        $sql1 = $this->connection->prepare("
            SELECT
                TO_BASE64(pc.`patient_id`) AS patientId,
                TO_BASE64(pc.`id`) AS patientChartId,
                p.`full_name` AS patientName,
                ct.`description` AS chartType,
               CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_ADD(:now, 
                    INTERVAL (
                    SELECT 
                        ct_sub.`expected_no_of_days`
                    FROM `chart_type` ct_sub
                    WHERE ct_sub.`description` = 'HSAT Results Interpretation' 
                        AND pc.`chart_type_id` = ct_sub.`id`
                        LIMIT 1
                ) DAY)), ' Day(s)'
               ) AS dayOverDue 
            FROM `patient_chart` pc
            LEFT JOIN `patient` p ON p.`id` = pc.`patient_id`
            LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
            LEFT JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
            WHERE pc.`is_deleted` = 0
            AND ct.`description` = 'HSAT Results Interpretation'
            AND cs.`description` != 'Completed'
            AND p.`referral_date` IS NOT NULL
            AND IF (
                pc.`id` IS NOT NULL,
                DATEDIFF(pc.`chart_date`, p.`referral_date`) > ct.`expected_no_of_days`,
               DATE_ADD(p.`referral_date`, INTERVAL (
                SELECT 
                    ct_sub.`expected_no_of_days`
                     FROM `chart_type` ct_sub
                     WHERE ct_sub.`description` = 'CPAP Setup' 
                     LIMIT 1
               ) DAY) < :now
            )
            AND pc.`provider_id` = :provider_id
        ");

        $sql2 = $this->connection->prepare("
            SELECT
                TO_BASE64(pc.`patient_id`) AS patientId,
                TO_BASE64(pc.`id`) AS patientChartId,
                p.`full_name` AS patientName,
                ct.`description` AS chartType,
               CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_ADD(:now, 
                    INTERVAL (
                    SELECT 
                        ct_sub.`expected_no_of_days`
                    FROM `chart_type` ct_sub
                    WHERE ct_sub.`description` = 'CPAP Setup' 
                        AND pc.`chart_type_id` = ct_sub.`id`
                        LIMIT 1
                ) DAY)), ' Day(s)'
               ) AS dayOverDue 
            FROM `patient_chart` pc
            LEFT JOIN `patient` p ON p.`id` = pc.`patient_id`
            LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
            LEFT JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
            WHERE pc.`is_deleted` = 0
            AND ct.`description` = 'CPAP Setup'
            AND cs.`description` != 'Completed'
            AND p.`referral_date` IS NOT NULL
            AND IF (
                pc.`id` IS NOT NULL,
                DATEDIFF(pc.`chart_date`, p.`referral_date`) > ct.`expected_no_of_days`,
               DATE_ADD(p.`referral_date`, INTERVAL (
                SELECT 
                    ct_sub.`expected_no_of_days`
                     FROM `chart_type` ct_sub
                     WHERE ct_sub.`description` = 'CPAP Setup Sold' 
                     LIMIT 1
               ) DAY) < :now
            )
            AND pc.`provider_id` = :provider_id
        ");

        $sql3 = $this->connection->prepare("
            SELECT
                TO_BASE64(pc.`patient_id`) AS patientId,
                TO_BASE64(pc.`id`) AS patientChartId,
                p.`full_name` AS patientName,
                ct.`description` AS chartType,
               CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_ADD(:now, 
                    INTERVAL (
                    SELECT 
                        ct_sub.`expected_no_of_days`
                    FROM `chart_type` ct_sub
                    WHERE ct_sub.`description` = 'CPAP Setup Sold' 
                        AND pc.`chart_type_id` = ct_sub.`id`
                        LIMIT 1
                ) DAY)), ' Day(s)'
               ) AS dayOverDue 
            FROM `patient_chart` pc
            LEFT JOIN `patient` p ON p.`id` = pc.`patient_id`
            LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
            LEFT JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
            WHERE pc.`is_deleted` = 0
            AND ct.`description` = 'CPAP Setup Sold'
            AND cs.`description` != 'Completed'
            AND p.`referral_date` IS NOT NULL
            AND IF (
                pc.`id` IS NOT NULL,
                DATEDIFF(pc.`chart_date`, p.`referral_date`) > ct.`expected_no_of_days`,
               DATE_ADD(p.`referral_date`, INTERVAL (
                SELECT 
                    ct_sub.`expected_no_of_days`
                     FROM `chart_type` ct_sub
                     WHERE ct_sub.`description` = 'CPAP Eval' 
                     LIMIT 1
               ) DAY) < :now
            )
            AND pc.`provider_id` = :provider_id
        ");

        $sql4 = $this->connection->prepare("
            SELECT
                TO_BASE64(pc.`patient_id`) AS patientId,
                TO_BASE64(pc.`id`) AS patientChartId,
                p.`full_name` AS patientName,
                ct.`description` AS chartType,
               CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_ADD(:now, 
                    INTERVAL (
                    SELECT 
                        ct_sub.`expected_no_of_days`
                    FROM `chart_type` ct_sub
                    WHERE ct_sub.`description` = 'CPAP Eval' 
                        AND pc.`chart_type_id` = ct_sub.`id`
                        LIMIT 1
                ) DAY)), ' Day(s)'
               ) AS dayOverDue 
            FROM `patient_chart` pc
            LEFT JOIN `patient` p ON p.`id` = pc.`patient_id`
            LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
            LEFT JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
            WHERE pc.`is_deleted` = 0
            AND ct.`description` = 'CPAP Eval'
            AND cs.`description` != 'Completed'
            AND p.`referral_date` IS NOT NULL
            AND IF (
                pc.`id` IS NOT NULL,
                DATEDIFF(pc.`chart_date`, p.`referral_date`) > ct.`expected_no_of_days`,
               DATE_ADD(p.`referral_date`, INTERVAL (
                SELECT 
                    ct_sub.`expected_no_of_days`
                     FROM `chart_type` ct_sub
                     WHERE ct_sub.`description` = 'CPAP End Eval' 
                     LIMIT 1
               ) DAY) < :now
            )
            AND pc.`provider_id` = :provider_id
        ");

        $sql5 = $this->connection->prepare("
            SELECT
                TO_BASE64(pc.`patient_id`) AS patientId,
                TO_BASE64(pc.`id`) AS patientChartId,
                p.`full_name` AS patientName,
                ct.`description` AS chartType,
               CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_ADD(:now, 
                    INTERVAL (
                    SELECT 
                        ct_sub.`expected_no_of_days`
                    FROM `chart_type` ct_sub
                    WHERE ct_sub.`description` = 'CPAP End Eval' 
                        AND pc.`chart_type_id` = ct_sub.`id`
                        LIMIT 1
                ) DAY)), ' Day(s)'
               ) AS dayOverDue 
            FROM `patient_chart` pc
            LEFT JOIN `patient` p ON p.`id` = pc.`patient_id`
            LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
            LEFT JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
            WHERE pc.`is_deleted` = 0
            AND ct.`description` = 'CPAP End Eval'
            AND cs.`description` != 'Completed'
            AND p.`referral_date` IS NOT NULL
            AND pc.`provider_id` = :provider_id
        ");

        $params = array(
            'provider_id' => $provider_id,
            'now' => date('Y-m-d 00:00:00')
        );

        $sql1->execute($params);
        $sql2->execute($params);
        $sql3->execute($params);
        $sql4->execute($params);
        $sql5->execute($params);

        $result['hri'] = $sql1->fetchAll();
        $result['cs'] = $sql2->fetchAll();
        $result['css'] = $sql3->fetchAll();
        $result['ce'] = $sql4->fetchAll();
        $result['cee'] = $sql5->fetchAll();

        return $result;
    }

    private function getPPO()
    {

        $sql = $this->connection->prepare("
            SELECT * FROM purchase_order po
            WHERE po.status IN (:status1, :status2)
        ");
        $params = array(
            'status1' => 'New',
            'status2' => 'Received',
        );
        $sql->execute($params);

        return $sql->rowCount();
    }

    private function getPayables()
    {

        $sql = $this->connection->prepare("
            SELECT * FROM purchase_invoice pi
            WHERE pi.status IN (:status1)
        ");
        $params = array(
            'status1' => 'Pending',
        );
        $sql->execute($params);

        return $sql->rowCount();

    }

}