<?php

namespace RxConnexionBundle\Repository;

/**
 * ReferralRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class ReferralRepository extends \Doctrine\ORM\EntityRepository
{
    public function validate($referral_form) {

        $errors = array();

        $action = $referral_form['action'];

        // d = delete
        if($action !== 'd') {

            $query = $this->getEntityManager()->getConnection()->prepare("
                SELECT `id`
                FROM `patient` r 
                WHERE r.`referral_id` != :id
                AND r.`first_name` = :firstName
                AND r.`last_name` = :lastName
                AND DATE_FORMAT(r.`birth_date`, '%m/%d/%Y') = :birthDate
                AND r.`is_deleted` = 0
                LIMIT 1
            ");
            $query->execute(array(
                'id' => $referral_form['id'],
                'firstName' => $referral_form['first_name'],
                'lastName' => $referral_form['last_name'],
                'birthDate' => $referral_form['birth_date']
            ));
            $query = $query->fetchAll();
            if(isset($query[0])) {
                $errors[] = 'Patient already existing.';
            }

            $referralDateExploded = explode('/', $referral_form['referral_date']);
            if(!checkdate($referralDateExploded[0], $referralDateExploded[1], $referralDateExploded[2])){
                $errors[] = 'Invalid referral date format ' . '(' . $referral_form['referral_date'] . ').';
            }

            $birthDateExploded = explode('/', $referral_form['birth_date']);
            if(!checkdate($birthDateExploded[0], $birthDateExploded[1], $birthDateExploded[2])){
                $errors[] = 'Invalid birth date format ' . '(' . $referral_form['birth_date'] . ').';
            }
        }

        return $errors;
    }

    public function ajax_list(array $get){

        $columns = array(
            array('r.`referral_date`', "DATE_FORMAT(r.`referral_date`, '%m/%d/%Y')", 'referral_date'),
            array('r.`full_name`', 'r.`full_name`'),
            array('r.`gender`', 'r.`gender`'),
            array('r.`birth_date`', "DATE_FORMAT(r.`birth_date`, '%m/%d/%Y')", 'birthDate'),
            array('r.`address`', 'r.`address`'),
            array("CONCAT_WS('<br />', r.`phone_no` , r.`phone_no_two`, r.`phone_no_three`)", "CONCAT_WS('<br />', r.`phone_no` , r.`phone_no_two`, r.`phone_no_three`)", 'phoneNos'),
            array("CONCAT_WS('<br />', r.`mobile_no` , r.`mobile_no_two`, r.`mobile_no_three`)", "CONCAT_WS('<br />', r.`mobile_no` , r.`mobile_no_two`, r.`mobile_no_three`)", 'mobileNos'),
            array('rp.`full_name`', 'rp.`full_name`', 'referring_provider'),
            array('rhf.`description`', 'rhf.`description`', 'referring_health_facility'),
            array('hf.`description`', 'hf.`description`', 'branch'),
            array('r.`is_approved`', 'r.`is_approved`'),
            array('r.`id`', 'r.`id`'),
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `referral` r";
        $joins = " LEFT JOIN `referring_provider` rp ON rp.`id` = r.`referring_provider_id`";
        $joins .= " LEFT JOIN `referring_health_facility` rhf ON rhf.`id` = r.`referring_health_facility_id`";
        $joins .= " LEFT JOIN `health_facility` hf ON hf.`id` = r.`health_facility_id`";
        $sqlWhere = "WHERE r.`is_deleted` = 0";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array();

        foreach($columns as $key => $column) {
            $select .= ($key > 0 ? ', ' : ' ') . $column[1] . (isset($column[2]) ? ' AS ' . $column[2] : '');
        }

        /*
         * Ordering
         */
        foreach($get['columns'] as $key => $column) {
            if($column['orderable']=='true') {
                if(isSet($get['order'])) {
                    foreach($get['order'] as $order) {
                        if($order['column']==$key) {
                            $orderBy .= (!empty($orderBy) ? ', ' : 'ORDER BY ') . $columns[$key][0] . (!empty($order['dir']) ? ' ' . $order['dir'] : '');
                        }
                    }
                }
            }
        }

        /*
         * Filtering
         */
        if(isset($get['search']) && $get['search']['value'] != ''){
            $aLikes = array();
            foreach($get['columns'] as $key => $column) {
                if($column['searchable']=='true') {
                    $aLikes[] = $columns[$key][0] . ' LIKE :searchValue';
                }
            }
            foreach($asColumns as $asColumn) {
                $aLikes[] = $asColumn . ' LIKE :searchValue';
            }
            if(count($aLikes)) {
                $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . '(' . implode(' OR ', $aLikes) . ')';
                $stmtParams['searchValue'] = "%" . $get['search']['value'] . "%";
            }
        }
        if($get['month'] != "all"){
            $yearMonth = explode("|",$get['month']);
            $year = $yearMonth[0];
            $month = $yearMonth[1];
            $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' YEAR(r.`created_at`) = :year AND MONTH(r.`created_at`) = :month ';
            $stmtParams['year'] = $year;
            $stmtParams['month'] = $month;
        }

        /* Set Limit and Length */
        if(isset( $get['start'] ) && $get['length'] != '-1'){
            $limit = 'LIMIT ' . (int)$get['start'] . ',' . (int)$get['length'];
        }

        $sql = "$select $from $joins $sqlWhere $groupBy $orderBy";
        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);
        $result_count = $query->fetchAll();

        $sql = "$select $from $joins $sqlWhere $groupBy $orderBy $limit";
        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);
        $result = $query->fetchAll();

        return array(
            'results' => $result,
            'total' => count($result_count)
        );
    }

    //REPORT REFERRAL
    public function reportReferral($params) {
        $sqlWhere = "WHERE (pat.`is_deleted` = 0 OR pat.`is_deleted` IS NOT NULL)";
        $stmtParams = array();

        if (!is_null($params['date_from']) && $params['date_from'] !== 'NULL' && $params['date_from'] !== 'all' && !empty($params['date_from']) && !is_null($params['date_to']) && $params['date_to'] !== 'null' && $params['date_to'] !== 'all' && !empty($params['date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pat.`referral_date` BETWEEN :date_from AND :date_to";
            $stmtParams['date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
            $stmtParams['date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['date_to'])));
        }

        if (!is_null($params['receive_date_from']) && $params['receive_date_from'] !== 'NULL' && $params['receive_date_from'] !== 'all' && !empty($params['receive_date_from']) && !is_null($params['receive_date_to']) && $params['receive_date_to'] !== 'null' && $params['receive_date_to'] !== 'all' && !empty($params['receive_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pat.`receive_date` BETWEEN :receive_date_from AND :receive_date_to";
            $stmtParams['receive_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['receive_date_from'])));
            $stmtParams['receive_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['receive_date_to'])));
        }

        if(isset($params['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf.`company_id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_ors) ";
            }
        }

        if(isset($params['branch']) && !empty($params['branch']) && 'NULL' !== strtoupper($params['branch'])){
            $branches = explode(',' , $params['branch']);
            if(count($branches) > 0){
                $hf_ors = '';
                foreach($branches as $key => $branchId){
                    $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "hf.`id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

        if(isset($params['patient']) && !empty($params['patient']) && 'NULL' !== strtoupper($params['patient'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pat.`id` = :patient";
            $stmtParams['patient'] = $params['patient'];
        }

        if(isset($params['clinician']) && !empty($params['clinician']) && 'NULL' !== strtoupper($params['clinician'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pc.`provider_id` = :clinician";
            $stmtParams['clinician'] = $params['clinician'];
        }

        if(isset($params['referring_affiliate']) && !empty($params['referring_affiliate']) && 'NULL' !== strtoupper($params['referring_affiliate'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "prhf.`referring_health_facility_id` = :referring_affiliate";
            $stmtParams['referring_affiliate'] = $params['referring_affiliate'];
        }

        if(isset($params['referring_physician']) && !empty($params['referring_physician']) && 'NULL' !== strtoupper($params['referring_physician'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "prp.`referring_provider_id` = :referring_physician";
            $stmtParams['referring_physician'] = $params['referring_physician'];
        }

        if(isset($params['agent']) && !empty($params['agent']) && 'NULL' !== strtoupper($params['agent'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "sa.`id` = :agent";
            $stmtParams['agent'] = $params['agent'];
        }

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

        $sql1 = "
            SELECT
                  pat.`referral_id` referralId,
                  DATE_FORMAT(pat.`referral_date`, '%m/%d/%Y') referral_date,
                  DATE_FORMAT(pat.`receive_date`, '%m/%d/%Y') receive_date,
                  pat.`id` patientId,
                  pat.`full_name` patient,
                  ct.`description` chartType,
                  cs.`description` chartStatus,
                  pn.`note`,
                  hf.`description` branch,
                  rhf.`description` referring_affiliate,
                  rp.`full_name` referring_physician,
                  rp.`fax_no` faxNo,
                  rp.`phone_no` phoneNo,
                  sa.`full_name` sales_agent,
                  sa.`id` sales_agent_id,
                  au.`full_name` accountUser,
                  pl.`letter_type` letterType,
                  pls.`description` letterStatus
            FROM `patient` pat
            LEFT JOIN `account_user` au ON au.`username` = pat.`created_by`
            LEFT JOIN `patient_chart` pc ON pc.`id` = (
              SELECT pc1.`id`
              FROM `patient_chart` pc1
              WHERE pc1.`patient_id` = pat.`id`
              AND pc1.`is_deleted` = 0
              ORDER BY pc1.`chart_date` DESC
              LIMIT 1
            )
            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 `provider` pro ON pc.`provider_id` = pro.`id`
            LEFT JOIN `patient_letter` pl ON pl.`id` = (
              SELECT pl1.`id`
              FROM `patient_letter` pl1
              WHERE pl1.`patient_id` = pat.`id`
              AND (pl.`is_deleted` = 0 OR pl.`is_deleted` IS NOT NULL)
              ORDER BY pl1.`created_at` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_letter_status` pls ON pl.`patient_letter_status_id` = pls.`id`
            LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                      SELECT
                          phf1.`id`
                       FROM `patient_health_facility` phf1
                       WHERE phf1.`end_date` IS NULL
                       AND (phf1.`is_deleted` = 0 OR phf1.`is_deleted` IS NOT NULL)
                       AND phf1.`patient_id` = pat.`id`
                       ORDER BY phf1.`created_at` DESC
                       LIMIT 1
            )
            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
            LEFT JOIN `company` co ON hf.`company_id` = co.`id`
            LEFT JOIN `patient_referring_health_facility` prhf ON prhf.`id` = (
              SELECT
                prhf1.`id`
              FROM `patient_referring_health_facility` prhf1
              WHERE prhf1.`patient_id` = pat.`id`
              AND (prhf1.`is_deleted` = 0 OR prhf1.`is_deleted` IS NOT NULL)
              AND prhf1.`end_date` IS NULL
              ORDER BY prhf1.`start_date` DESC 
              LIMIT 1
            )
            LEFT JOIN `referring_health_facility` rhf ON  rhf.`id` = prhf.`referring_health_facility_id`
            LEFT JOIN `patient_referring_provider` prp ON prp.`id` = (
              SELECT prp1.`id`
              FROM `patient_referring_provider` prp1
              WHERE prp1.`patient_id` = pat.`id`
              AND (prp1.`is_deleted` = 0 OR prp1.`is_deleted` IS NOT NULL)
              AND prp1.`end_date` IS NULL
              ORDER BY prp1.`start_date` DESC 
              LIMIT 1
            )
            LEFT JOIN `referring_provider` rp ON rp.`id` = prp.`referring_provider_id`
            LEFT JOIN `patient_note` pn ON pn.`id` = (SELECT pnt.`id` 
                                                      FROM `patient_note` pnt
                                                      WHERE pnt.`patient_id` = pat.`id` 
                                                      AND (pnt.`is_deleted` = 0 OR pnt.`is_deleted` IS NOT NULL)
                                                      ORDER BY pnt.`created_at`   
                                                      DESC LIMIT 1)                                  
            LEFT JOIN `sales_agent_referring_health_facility` sarhf ON sarhf.`id` = (
              SELECT sarhf1.`id`
              FROM `sales_agent_referring_health_facility` sarhf1
              WHERE sarhf1.`referring_health_facility_id` = rhf.`id`
              AND (sarhf1.`is_deleted` = 0 OR sarhf1.`is_deleted` IS NOT NULL)
              AND sarhf1.`end_date` IS NULL
              ORDER BY sarhf1.`start_date` DESC 
              LIMIT 1
            )
            LEFT JOIN `sales_agent` sa ON sa.`id` = sarhf.`sales_agent_id`                                                                                                                                                     
            $sqlWhere
            AND pat.`is_duplicate` = 0
            AND (pat.`is_not_count_as_referral` = 0 OR pat.`is_not_count_as_referral` IS NULL)
            AND pat.`id` IS NOT NULL
            AND hf.`description` IS NOT NULL
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            GROUP BY pat.`id`
            ORDER BY pat.`referral_date` ASC
        ";

        $sql2 = "
            SELECT
                  rp.`full_name` referringProvider,
                  COUNT(DISTINCT pat.`id`) referralCount
            FROM `patient` pat
            LEFT JOIN `account_user` au ON au.`username` = pat.`created_by`
            LEFT JOIN `patient_chart` pc ON pc.`id` = (
              SELECT pc1.`id`
              FROM `patient_chart` pc1
              WHERE pc1.`patient_id` = pat.`id`
              AND pc1.`is_deleted` = 0
              ORDER BY pc1.`chart_date` DESC
              LIMIT 1
            )
            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 `provider` pro ON pc.`provider_id` = pro.`id`
            LEFT JOIN `patient_letter` pl ON pl.`id` = (
              SELECT pl1.`id`
              FROM `patient_letter` pl1
              WHERE pl1.`patient_id` = pat.`id`
              ORDER BY pl1.`created_at` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_letter_status` pls ON pl.`patient_letter_status_id` = pls.`id`
            LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                      SELECT
                          phf1.`id`
                       FROM `patient_health_facility` phf1
                       WHERE phf1.`end_date` IS NULL
                       AND phf1.`patient_id` = pat.`id`
                       ORDER BY phf1.`created_at` DESC
                       LIMIT 1
            )
            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
            LEFT JOIN `company` co ON hf.`company_id` = co.`id`
            LEFT JOIN `patient_referring_health_facility` prhf ON prhf.`id` = (
              SELECT
                prhf1.`id`
              FROM `patient_referring_health_facility` prhf1
              WHERE prhf1.`patient_id` = pat.`id`
              AND prhf1.`is_deleted` = 0
              AND prhf1.`end_date` IS NULL
              ORDER BY prhf1.`start_date` DESC 
              LIMIT 1
            )
            LEFT JOIN `referring_health_facility` rhf ON  rhf.`id` = prhf.`referring_health_facility_id`
            LEFT JOIN `patient_referring_provider` prp ON prp.`id` = (
              SELECT prp1.`id`
              FROM `patient_referring_provider` prp1
              WHERE prp1.`patient_id` = pat.`id`
              AND prp1.`is_deleted` = 0
              AND prp1.`end_date` IS NULL
              ORDER BY prp1.`start_date` DESC 
              LIMIT 1
            )
            LEFT JOIN `referring_provider` rp ON rp.`id` = prp.`referring_provider_id`
            LEFT JOIN `patient_note` pn ON pn.`id` = (SELECT pnt.`id` 
                                                      FROM `patient_note` pnt
                                                      WHERE pnt.`patient_id` = pat.`id` 
                                                      ORDER BY pnt.`created_at`   
                                                      DESC LIMIT 1)                                  
            LEFT JOIN `sales_agent_referring_health_facility` sarhf ON sarhf.`id` = (
              SELECT sarhf1.`id`
              FROM `sales_agent_referring_health_facility` sarhf1
              WHERE sarhf1.`referring_health_facility_id` = rhf.`id`
              AND sarhf1.`is_deleted` = 0
              AND sarhf1.`end_date` IS NULL
              ORDER BY sarhf1.`start_date` DESC 
              LIMIT 1
            )
            LEFT JOIN `sales_agent` sa ON sa.`id` = sarhf.`sales_agent_id` 
            $sqlWhere       
            AND pat.`is_duplicate` = 0
            AND (pat.`is_not_count_as_referral` = 0 OR pat.`is_not_count_as_referral` IS NULL)
            AND pat.`id` IS NOT NULL
            AND hf.`description` IS NOT NULL
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')      
            GROUP BY prp.`referring_provider_id`
        ";

        $query1 = $this->getEntityManager()->getConnection()->prepare($sql1);
        $query1->execute($stmtParams);

        $query2 = $this->getEntityManager()->getConnection()->prepare($sql2);
        $query2->execute($stmtParams);

        $result['referral'] = $query1->fetchAll();
        $result['referralCount'] = $query2->fetchAll();

        return $result;
    }

    //REPORT REFERRAL MARKETING
    public function reportReferralMarketing($params) {
        $sqlWhere = "WHERE (pat.`is_deleted` = 0 OR pat.`is_deleted` IS NULL)";
        $stmtParams = array();

        if (!is_null($params['date_from']) && $params['date_from'] !== 'null' && $params['date_from'] !== 'all' && !empty($params['date_from']) && !is_null($params['date_to']) && $params['date_to'] !== 'null' && $params['date_to'] !== 'all' && !empty($params['date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pat.`referral_date` BETWEEN :date_from AND :date_to";
            $stmtParams['date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
            $stmtParams['date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['date_to'])));
        }

        if (!is_null($params['receive_date_from']) && $params['receive_date_from'] !== 'NULL' && $params['receive_date_from'] !== 'all' && !empty($params['receive_date_from']) && !is_null($params['receive_date_to']) && $params['receive_date_to'] !== 'null' && $params['receive_date_to'] !== 'all' && !empty($params['receive_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pat.`receive_date` BETWEEN :receive_date_from AND :receive_date_to";
            $stmtParams['receive_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['receive_date_from'])));
            $stmtParams['receive_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['receive_date_to'])));
        }

        if(isset($params['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf.`company_id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_ors) ";
            }
        }

        if(isset($params['branch']) && !empty($params['branch']) && 'NULL' !== strtoupper($params['branch'])){
            $branches = explode(',' , $params['branch']);
            if(count($branches) > 0){
                $hf_ors = '';
                foreach($branches as $key => $branchId){
                    $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "phf.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

        if(isset($params['clinician']) && !empty($params['clinician']) && 'NULL' !== strtoupper($params['clinician'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pc.`provider_id` = :clinician";
            $stmtParams['clinician'] = $params['clinician'];
        }

        if(isset($params['referring_affiliate']) && !empty($params['referring_affiliate']) && 'NULL' !== strtoupper($params['referring_affiliate'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "rhf.`id` = :referring_affiliate";
            $stmtParams['referring_affiliate'] = $params['referring_affiliate'];
        }

        if(isset($params['referring_physician']) && !empty($params['referring_physician']) && 'NULL' !== strtoupper($params['referring_physician'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "rp.`id` = :referring_physician";
            $stmtParams['referring_physician'] = $params['referring_physician'];
        }

        if(isset($params['agent']) && !empty($params['agent']) && 'NULL' !== strtoupper($params['agent'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "sa.`id` = :agent";
            $stmtParams['agent'] = $params['agent'];
        }

        if($params['company'] == 'null' && $params['branch'] == 'null') {
            if (!is_null($params['userData']) && !empty($params['userData'])) {
                if ($params['userData']['type'] != 'Super Admin') {
                    if (count($params['userData']['company']['companyBranches']) > 0) {
                        $hf_ors = '';
                        foreach ($params['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) ";
                    }
                }
            }
        }

        $sql = "SELECT 
                    pat.`id`,
                    hf.`description` healthFacility,
                    rhf.`description` referringHealthFacility,
                    rp.`full_name` referringProvider,
                    pat.`full_name` patientName,
                    DATE_FORMAT(pat.`referral_date`, '%m/%d/%Y') referral_date,
                    DATE_FORMAT(pat.`receive_date`, '%m/%d/%Y') receive_date,
                    ct.`description` chartType,
                    cs.`description` chartStatus,
                    IF(cs.`description` = 'Sold', '1', '') sold,
                    IF(ct.`description` = 'CPAP Setup' AND cs.`description` = 'Sold', sa.`rate`, '') cpapSetupSold,
                    sa.`full_name` agent
                FROM `patient` pat
                LEFT JOIN `patient_referring_health_facility` prhf ON prhf.`id` = (
                    SELECT prhf2.`id`	
                    FROM `patient_referring_health_facility` prhf2
                    WHERE prhf2.`patient_id` = pat.`id`
                    AND prhf2.`end_date` IS NULL 
                    LIMIT 1
                )
                LEFT JOIN `referring_health_facility` rhf ON rhf.`id` = prhf.`referring_health_facility_id`
                LEFT JOIN `patient_referring_provider` prp ON prp.`id` = (
                    SELECT prp2.`id`	
                    FROM `patient_referring_provider` prp2
                    WHERE prp2.`patient_id` = pat.`id`
                    AND prp2.`end_date` IS NULL 
                    LIMIT 1
                )
                LEFT JOIN `referring_provider` rp ON rp.`id` = prp.`referring_provider_id`
                LEFT JOIN `patient_chart` pc ON pc.`id` = (
                    SELECT lpc.`id`
                    FROM `patient_chart` lpc
                    WHERE lpc.`patient_id` = pat.`id`
                    ORDER BY lpc.`chart_date` DESC
                    LIMIT 1
                )
                LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                      SELECT
                          phf1.`id`
                       FROM `patient_health_facility` phf1
                       WHERE phf1.`end_date` IS NULL
                       AND phf1.`patient_id` = pat.`id`
                       ORDER BY phf1.`created_at` DESC
                       LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON phf.`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 `sales_agent_referring_health_facility` sarhf ON sarhf.`referring_health_facility_id` = rhf.`id`
                LEFT JOIN `sales_agent` sa ON sa.`id` = sarhf.`sales_agent_id`       
                $sqlWhere       
                AND pat.`is_duplicate` = 0
                AND (pat.`is_not_count_as_referral` = 0 OR pat.`is_not_count_as_referral` IS NULL)
                AND pat.`id` IS NOT NULL
                AND hf.`description` IS NOT NULL
                AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                GROUP BY pat.`id`
                ORDER BY hf.`description` ASC, rhf.`description` ASC, rp.`full_name` ASC
                ";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    //PRODUCTIVITY NEW REFERRAL REPORT
    public function productivityNewReferralReport($params) {
        $sqlWhere1 = " WHERE DATE_FORMAT(ref.`created_at`, '%m/%d/%Y') BETWEEN DATE_FORMAT('".date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])))."', '%m/%d/%Y') AND DATE_FORMAT('".date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_to'])))."', '%m/%d/%Y')";
        $sqlWhere2 = "WHERE DATE_FORMAT(fax.`created_at`, '%m/%d/%Y') BETWEEN DATE_FORMAT('".date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])))."', '%m/%d/%Y') AND DATE_FORMAT('".date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_to'])))."', '%m/%d/%Y')";

//        if (!is_null($params['date_from']) && $params['date_from'] !== 'null' && $params['date_from'] !== 'all' && !empty($params['date_from']) && !is_null($params['date_to']) && $params['date_to'] !== 'null' && $params['date_to'] !== 'all' && !empty($params['date_to'])) {
//            $sqlWhere1 .= (empty($sqlWhere1) ? "WHERE " : " AND ") . "DATE_FORMAT(ref.`created_at`, '%m/%d/%Y %H:%i:%s') BETWEEN :date_from AND :date_to";
//            $stmtParams1['date_from'] = "DATE_FORMAT('".date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])))."', '%m/%d/%Y %H:%i:%s')";
//            $stmtParams1['date_to'] = "DATE_FORMAT('".date('Y-m-d 23:59:59', strtotime(str_replace('-',  '/', $params['date_to'])))."', '%m/%d/%Y %H:%i:%s')";
//        }
//
//        if (!is_null($params['date_from']) && $params['date_from'] !== 'null' && $params['date_from'] !== 'all' && !empty($params['date_from']) && !is_null($params['date_to']) && $params['date_to'] !== 'null' && $params['date_to'] !== 'all' && !empty($params['date_to'])) {
//            $sqlWhere2 .= (empty($sqlWhere2) ? "WHERE " : " AND ") . "DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') BETWEEN :date_from AND :date_to";
//            $stmtParams2['date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
//            $stmtParams2['date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['date_to'])));
//        }

        $sql_received = "
            SELECT 
               ref.`health_facility_id` AS healthFacility_id,
               hf.`description` AS healthFacility_desc,
               COUNT(ref.`id`) AS count_received
            FROM `referral` ref
            LEFT JOIN `fax_tran` fax ON fax.id = ref.`fax_tran_id`
            LEFT JOIN `health_facility` hf ON hf.`id` = fax.`health_facility_id`  
            $sqlWhere1
            AND fax.`fax_category` = 'Referral'
            AND hf.description NOT IN('Demo_Test', 'test', 'DemoLocation')
            AND ref.`health_facility_id` IS NOT NULL
            GROUP BY ref.`health_facility_id`
        ";

        $sql_encoded = "
            SELECT 
                ref.`health_facility_id` AS healthFacility_id,
                hf.`description` AS healthFacility_desc,
                COUNT(*) AS count_encoded
            FROM `referral` ref
            LEFT JOIN `health_facility` hf ON hf.`id` = ref.`health_facility_id`
            $sqlWhere1
            AND hf.description NOT IN('Demo_Test', 'test', 'DemoLocation')
            AND ref.`health_facility_id` IS NOT NULL       
            GROUP BY ref.`health_facility_id`
        ";

        $sql_isDuplicate = "
            SELECT 
      		   fax.`health_facility_id` AS healthFacility_id,
               hf.`description` AS healthFacility_desc,
               SUM(IF(fax.`is_duplicate` = 1, 1, 0)) AS count_isDuplicated
            FROM `fax_tran` fax
            LEFT JOIN `health_facility` hf ON hf.`id` = fax.`health_facility_id`  
            LEFT JOIN `referral` ref ON ref.`fax_tran_id` = fax.`id`
            $sqlWhere2
            AND hf.description NOT IN('Demo_Test', 'test', 'DemoLocation')
            AND fax.`health_facility_id` IS NOT NULL
            GROUP BY fax.`health_facility_id`
        ";

        $sql_isExisting = "
            SELECT 
      		   fax.`health_facility_id` AS healthFacility_id,
               hf.`description` AS healthFacility_desc,
               SUM(IF(fax.`is_existing` = 1, 1, 0)) AS count_isExisting
            FROM `fax_tran` fax
            LEFT JOIN `health_facility` hf ON hf.`id` = fax.`health_facility_id`  
            LEFT JOIN `referral` ref ON ref.`fax_tran_id` = fax.`id`
            $sqlWhere2
            AND hf.description NOT IN('Demo_Test', 'test', 'DemoLocation')
            AND fax.`health_facility_id` IS NOT NULL
            GROUP BY fax.`health_facility_id`
        ";

        $sql_isUnableToProcess = "
            SELECT 
      		   fax.`health_facility_id` AS healthFacility_id,
               hf.`description` AS healthFacility_desc,
               SUM(IF(fax.`is_unable_to_process` = 1, 1, 0)) AS count_isUnableToProcess
            FROM `fax_tran` fax
            LEFT JOIN `health_facility` hf ON hf.`id` = fax.`health_facility_id`  
            LEFT JOIN `referral` ref ON ref.`fax_tran_id` = fax.`id`
            $sqlWhere2
            AND hf.description NOT IN('Demo_Test', 'test', 'DemoLocation')
            AND fax.`health_facility_id` IS NOT NULL
            GROUP BY fax.`health_facility_id`
        ";

        $query1 = $this->getEntityManager()->getConnection()->prepare($sql_received);
        $query2 = $this->getEntityManager()->getConnection()->prepare($sql_encoded);
        $query3 = $this->getEntityManager()->getConnection()->prepare($sql_isDuplicate);
        $query4 = $this->getEntityManager()->getConnection()->prepare($sql_isExisting);
        $query5 = $this->getEntityManager()->getConnection()->prepare($sql_isUnableToProcess);

        $query1->execute();
        $query2->execute();
        $query3->execute();
        $query4->execute();
        $query5->execute();

        $referral_fax = $query1->fetchAll();
        $referrals = $query2->fetchAll();
        $faxIsDuplicated = $query3->fetchAll();
        $faxIsExisting = $query4->fetchAll();
        $faxIsUnableToProcess = $query5->fetchAll();

        $branchesSummary = array();


        foreach($referral_fax as $refFax) {

            $found = false;
            foreach($branchesSummary as $key => $bs) {
                if($bs['id'] == $refFax['healthFacility_id']) {
                    $branchesSummary[$key]['faxReceived'] = $refFax['count_received'];
                    $found = true;
                    break;
                }
            }

            if(!$found) {
                $branchesSummary[] = array(
                    'id' => $refFax['healthFacility_id'],
                    'description' => $refFax['healthFacility_desc'],
                    'faxReceived' => $refFax['count_received']
                );
            }
        }

        foreach($referrals as $ref) {

            $found = false;
            foreach($branchesSummary as $key => $bs) {
                if($bs['id'] == $ref['healthFacility_id']) {
                    $branchesSummary[$key]['referralCount'] = $ref['count_encoded'];
                    $found = true;
                    break;
                }
            }

            if(!$found) {
                $branchesSummary[] = array(
                    'id' => $ref['healthFacility_id'],
                    'description' => $ref['healthFacility_desc'],
                    'referralCount' => $ref['count_encoded']
                );
            }
        }

        foreach($faxIsDuplicated as $faxIsDup) {

            $found = false;
            foreach($branchesSummary as $key => $bs) {
                if($bs['id'] == $faxIsDup['healthFacility_id']) {
                    $branchesSummary[$key]['faxIsDuplicatedCount'] = $faxIsDup['count_isDuplicated'];
                    $found = true;
                    break;
                }
            }

            if(!$found) {
                $branchesSummary[] = array(
                    'id' => $faxIsDup['healthFacility_id'],
                    'description' => $faxIsDup['healthFacility_desc'],
                    'faxIsDuplicatedCount' => $faxIsDup['count_isDuplicated']
                );
            }
        }

        foreach($faxIsExisting as $faxIsExts) {

            $found = false;
            foreach($branchesSummary as $key => $bs) {
                if($bs['id'] == $faxIsExts['healthFacility_id']) {
                    $branchesSummary[$key]['faxIsExistingCount'] = $faxIsExts['count_isExisting'];
                    $found = true;
                    break;
                }
            }

            if(!$found) {
                $branchesSummary[] = array(
                    'id' => $faxIsExts['healthFacility_id'],
                    'description' => $faxIsExts['healthFacility_desc'],
                    'faxIsExistingCount' => $faxIsExts['count_isExisting']
                );
            }
        }

        foreach($faxIsUnableToProcess as $faxIsUtp) {

            $found = false;
            foreach($branchesSummary as $key => $bs) {
                if($bs['id'] == $faxIsUtp['healthFacility_id']) {
                    $branchesSummary[$key]['faxIsUnableToProcessCount'] = $faxIsUtp['count_isUnableToProcess'];
                    $found = true;
                    break;
                }
            }

            if(!$found) {
                $branchesSummary[] = array(
                    'id' => $faxIsUtp['healthFacility_id'],
                    'description' => $faxIsUtp['healthFacility_desc'],
                    'faxIsUnableToProcessCount' => $faxIsUtp['count_isUnableToProcess']
                );
            }
        }

        return $branchesSummary;
    }

    //PRODUCTIVITY STAFF REFERRAL REPORT
    public function productivityStaffReferralReport($params) {

        $sqlWhere1 = " WHERE DATE_FORMAT(ref.`created_at`, '%m/%d/%Y') BETWEEN DATE_FORMAT('".date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])))."', '%m/%d/%Y') AND DATE_FORMAT('".date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_to'])))."', '%m/%d/%Y')";
        $sqlWhere2 = "WHERE DATE_FORMAT(fax.`created_at`, '%m/%d/%Y') BETWEEN DATE_FORMAT('".date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])))."', '%m/%d/%Y') AND DATE_FORMAT('".date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_to'])))."', '%m/%d/%Y')";

        $sql_received = "
            SELECT 
               fax.`account_user_id` accountUserId,
               au.`full_name` fullName,
               COUNT(*) count_received
            FROM `referral` ref
            LEFT JOIN `fax_tran` fax ON fax.id = ref.`fax_tran_id`
            LEFT JOIN `account_user` au ON au.`id` = fax.`account_user_id` 
            $sqlWhere1
            AND fax.`fax_category` = 'Referral'
            AND fax.`account_user_id` IS NOT NULL
            GROUP BY fax.`account_user_id`
        ";

        $sql_encoded = "
            SELECT 
                au.`id` accountUserId,
                au.`full_name` fullName,
                COUNT(*) count_encoded
            FROM `referral` ref
            LEFT JOIN `account_user` au ON au.`username` = ref.`created_by`
            $sqlWhere1
            AND au.`id` IS NOT NULL
            GROUP BY au.`id`
        ";

        $sql_isDuplicate = "
            SELECT 
                au.`id` accountUserId,
                au.`full_name` fullName,
                SUM(IF(fax.`is_duplicate` = 1, 1, 0)) count_isDuplicated
            FROM `fax_tran` fax
            LEFT JOIN `account_user` au ON au.`id` = fax.`account_user_id`
            $sqlWhere2
            AND (au.`is_deleted` = 0 OR au.`is_deleted` IS NOT NULL)
         	AND au.`id` IS NOT NULL
         	GROUP BY au.`id`
        ";

        $sql_isExisting = "
            SELECT 
                au.`id` accountUserId,
                au.`full_name` fullName,
                SUM(IF(fax.`is_existing` = 1, 1, 0)) count_isExisting
            FROM `fax_tran` fax
            LEFT JOIN `account_user` au ON au.`id` = fax.`account_user_id`
            $sqlWhere2
            AND (au.`is_deleted` = 0 OR au.`is_deleted` IS NOT NULL)
         	AND au.`id` IS NOT NULL
         	GROUP BY au.`id`
        ";

        $sql_isUnableToProcess = "
            SELECT 
                au.`id` accountUserId,
                au.`full_name` fullName,
                SUM(IF(fax.`is_unable_to_process` = 1, 1, 0)) count_isUnableToProcess
            FROM `fax_tran` fax
            LEFT JOIN `account_user` au ON au.`id` = fax.`account_user_id`
            $sqlWhere2
            AND (au.`is_deleted` = 0 OR au.`is_deleted` IS NOT NULL)
         	AND au.`id` IS NOT NULL
         	GROUP BY au.`id`
        ";

        $query1 = $this->getEntityManager()->getConnection()->prepare($sql_received);
        $query2 = $this->getEntityManager()->getConnection()->prepare($sql_encoded);
        $query3 = $this->getEntityManager()->getConnection()->prepare($sql_isDuplicate);
        $query4 = $this->getEntityManager()->getConnection()->prepare($sql_isExisting);
        $query5 = $this->getEntityManager()->getConnection()->prepare($sql_isUnableToProcess);

        $query1->execute();
        $query2->execute();
        $query3->execute();
        $query4->execute();
        $query5->execute();

        $referral_fax = $query1->fetchAll();
        $referrals = $query2->fetchAll();
        $faxIsDuplicated = $query3->fetchAll();
        $faxIsExisting = $query4->fetchAll();
        $faxIsUnableToProcess = $query5->fetchAll();

        $staffSummary = array();

        foreach($referral_fax as $fax) {

            $found = false;
            foreach($staffSummary as $key => $bs) {
                if($bs['id'] == $fax['accountUserId']) {
                    $staffSummary[$key]['faxCount'] = $fax['count_received'];
                    $found = true;
                    break;
                }
            }

            if(!$found) {
                $staffSummary[] = array(
                    'id' => $fax['accountUserId'],
                    'fullName' => $fax['fullName'],
                    'faxCount' => $fax['count_received']
                );
            }
        }



        foreach($referrals as $ref) {

            $found = false;
            foreach($staffSummary as $key => $bs) {
                if($bs['id'] == $ref['accountUserId']) {
                    $staffSummary[$key]['referralCount'] = $ref['count_encoded'];
                    $found = true;
                    break;
                }
            }

            if(!$found) {
                $staffSummary[] = array(
                    'id' => $ref['accountUserId'],
                    'fullName' => $ref['fullName'],
                    'referralCount' => $ref['count_encoded']
                );
            }
        }



        foreach($faxIsDuplicated as $faxIsDup) {

            $found = false;
            foreach($staffSummary as $key => $bs) {
                if($bs['id'] == $faxIsDup['accountUserId']) {
                    $staffSummary[$key]['faxIsDuplicatedCount'] = $faxIsDup['count_isDuplicated'];
                    $found = true;
                    break;
                }
            }

            if(!$found) {
                $staffSummary[] = array(
                    'id' => $faxIsDup['accountUserId'],
                    'fullName' => $faxIsDup['fullName'],
                    'faxIsDuplicatedCount' => $faxIsDup['count_isDuplicated']
                );
            }
        }

        foreach($faxIsExisting as $faxIsExts) {

            $found = false;
            foreach($staffSummary as $key => $bs) {
                if($bs['id'] == $faxIsExts['accountUserId']) {
                    $staffSummary[$key]['faxIsExistingCount'] = $faxIsExts['count_isExisting'];
                    $found = true;
                    break;
                }
            }

            if(!$found) {
                $staffSummary[] = array(
                    'id' => $faxIsExts['accountUserId'],
                    'fullName' => $faxIsExts['fullName'],
                    'faxIsExistingCount' => $faxIsExts['count_isExisting']
                );
            }
        }

        foreach($faxIsUnableToProcess as $faxIsUtp) {

            $found = false;
            foreach($staffSummary as $key => $bs) {
                if($bs['id'] == $faxIsUtp['accountUserId']) {
                    $staffSummary[$key]['faxIsUnableToProcessCount'] = $faxIsUtp['count_isUnableToProcess'];
                    $found = true;
                    break;
                }
            }

            if(!$found) {
                $staffSummary[] = array(
                    'id' => $faxIsUtp['accountUserId'],
                    'fullName' => $faxIsUtp['fullName'],
                    'faxIsUnableToProcessCount' => $faxIsUtp['count_isUnableToProcess']
                );
            }
        }

        return $staffSummary;
    }

    //REPORT SPECIALISTS` REFERRAL TRACKER
    public function reportSpecialistReferralTracker($params) {
        $sqlWhere = "WHERE (ps.`is_deleted` = 0 OR ps.`is_deleted` IS NULL)";
        $stmtParams = array();

//        if (!is_null($params['date_from']) && $params['date_from'] !== 'NULL' && $params['date_from'] !== 'all' && !empty($params['date_from']) && !is_null($params['date_to']) && $params['date_to'] !== 'null' && $params['date_to'] !== 'all' && !empty($params['date_to'])) {
//            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pat.`referral_date` BETWEEN :date_from AND :date_to";
//            $stmtParams['date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
//            $stmtParams['date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['date_to'])));
//        }

        if(isset($params['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf.`company_id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_ors) ";
            }
        }

        if(isset($params['branch']) && !empty($params['branch']) && 'NULL' !== strtoupper($params['branch'])){
            $branches = explode(',' , $params['branch']);
            if(count($branches) > 0){
                $hf_ors = '';
                foreach($branches as $key => $branchId){
                    $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "phf.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

        if(isset($params['servicing_branch']) && !empty($params['servicing_branch']) && 'NULL' !== strtoupper($params['servicing_branch'])){
            $sbranches = explode(',' , $params['servicing_branch']);
            if(count($sbranches) > 0){
                $shf_ors = '';
                foreach($sbranches as $key => $sbranchId){
                    $shf_ors .= (empty($shf_ors) ? '' : ' OR ') . "ps.`health_facility_id` = :servicing_healthFacility_id_$key";
                    $stmtParams["servicing_healthFacility_id_$key"] = $sbranchId;
                }
                $sqlWhere .= " AND ($shf_ors) ";
            }
        }

        if(isset($params['patient']) && !empty($params['patient']) && 'NULL' !== strtoupper($params['patient'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pat.`id` = :patient";
            $stmtParams['patient'] = $params['patient'];
        }

        if(isset($params['service']) && !empty($params['service']) && 'NULL' !== strtoupper($params['service'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "ps.`service_id` = :service";
            $stmtParams['service'] = $params['service'];
        }

        if(isset($params['specialist']) && !empty($params['specialist']) && 'NULL' !== strtoupper($params['specialist'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "ps.`specialist_id` = :specialist";
            $stmtParams['specialist'] = $params['specialist'];
        }

        if(isset($params['type']) && !empty($params['type']) && $params['type'] != 'All'  && 'NULL' !== strtoupper($params['type'])){
            if ($params['type'] == 'Others') {
                $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "(ps.`patient_service_type` = :type OR ps.`patient_service_type` IS NULL OR ps.`patient_service_type` = '')";
            } else {
                $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "ps.`patient_service_type` = :type";
            }
            $stmtParams['type'] = $params['type'];
        }

        if($params['company'] == 'null' && $params['branch'] == 'null' && $params['servicing_branch'] == 'null') {
            if (!is_null($params['userData']) && !empty($params['userData'])) {
                if ($params['userData']['type'] != 'Super Admin') {
                    if (count($params['userData']['company']['companyBranches']) > 0) {
                        $hf_ors = '';
                        foreach ($params['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) ";
                    }
                }
            }
        }


        $sql = "
                SELECT 
                    pat.`id` AS patientId,
                    pat.`full_name` AS patientName,
                    pt.`description` AS patientType,
                    hf.`description` AS healthFacility,
                    shf.`description` AS servicingHealthFacility,
                    ser.`description` AS serviceDesc,
                    DATE_FORMAT(ps.`start_date`, '%m/%d/%Y') AS startDate,
                    DATE_FORMAT(ps.`end_date`, '%m/%d/%Y') AS endDate,
                    s.`full_name` AS specialistFullName,					 					                 
                    pctm.`current_ahi` AS ahi,
                    pctm.`initial_ahi` AS init_ahi,
                    pc.`bmi`,
                    pc.`otwo_saturation`,
                    d.`description` AS diagnosisDesc,
                    prov.`full_name` AS providerFullName,
                    DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') AS chartDate,
                    ct.`description` AS chartType,
                    cs.`description` AS chartStatus,
                    DATE_FORMAT(sp_pc.`chart_date`, '%m/%d/%Y') AS sp_chartDate,
                    sp_ct.`description` AS sp_chartType,
                    sp_cs.`description` AS sp_chartStatus,
                    CONCAT('[ ' , DATE_FORMAT(pn.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', pn.`created_by`, ' ] : ', pn.`note`) AS note
                FROM `patient_service` ps
                LEFT JOIN `patient` pat ON pat.`id` = ps.`patient_id`
                LEFT JOIN `specialist` s ON ps.`specialist_id` = s.`id`
                LEFT JOIN `service` ser ON ps.`service_id` = ser.`id`
                LEFT JOIN `health_facility` shf ON ps.`health_facility_id` = shf.`id`
                LEFT JOIN `patient_chart` pc ON pc.`id` = (
                        SELECT lpc.`id`
                        FROM `patient_chart` lpc
                        LEFT JOIN `health_facility` hfpc ON lpc.`health_facility_id` = hfpc.`id`
                        LEFT JOIN `chart_type` ctt ON lpc.`chart_type_id` = ctt.`id`
                        LEFT JOIN `chart_status` cst ON lpc.`chart_status_id` = cst.`id`
                        WHERE lpc.`patient_id` = pat.`id`
                        AND lpc.`is_deleted` = 0
                        ORDER BY lpc.`chart_date` DESC
                        LIMIT 1
                )
                LEFT JOIN `patient_chart` sp_pc ON sp_pc.`id` = (
                        SELECT sp_pc1.`id`
                        FROM `patient_chart` sp_pc1
                        LEFT JOIN `health_facility` sp_hf ON sp_pc1.`health_facility_id` = sp_hf.`id`
                        LEFT JOIN `chart_type` sp_ct ON sp_pc1.`chart_type_id` = sp_ct.`id`
                        LEFT JOIN `chart_status` sp_cs ON sp_pc1.`chart_status_id` = sp_cs.`id`
                        WHERE sp_pc1.`patient_id` = pat.`id`
                        AND sp_ct.`description` = 'Specialist Referral'
                        AND sp_pc1.`is_deleted` = 0
                        ORDER BY sp_pc1.`chart_date` DESC
                        LIMIT 1
                )
                LEFT JOIN `provider` prov ON pc.`provider_id` = prov.`id`
                LEFT JOIN `patient_type` pt ON pat.`patient_type_id` = pt.`id`
                LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                          SELECT
                              phf1.`id`
                          FROM `patient_health_facility` phf1
                          WHERE phf1.`end_date` IS NULL
                          AND (phf1.`is_deleted` = 0 OR phf1.`is_deleted` IS NOT NULL)
                          AND phf1.`patient_id` = pat.`id`
                          ORDER BY phf1.`created_at` DESC
                          LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                LEFT JOIN `patient_chart_type_method` pctm ON pctm.`id` = (
                        SELECT pctmt.`id`
                        FROM `patient_chart_type_method` pctmt
                        LEFT JOIN `patient_chart` pct ON pctmt.`patient_chart_id` = pct.`id`
                        LEFT JOIN `chart_status` cstt ON pct.`chart_status_id` = cstt.`id`
                        WHERE pctmt.`current_ahi` IS NOT NULL
                        AND pct.`patient_id` = pat.`id`
                        AND pct.`is_deleted` = 0
                        AND cstt.`description` NOT IN ('No Show', 'Cancelled')
                        ORDER BY pct.`chart_date` DESC
                        LIMIT 1
                )
                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 `chart_type` sp_ct ON sp_pc.`chart_type_id` = sp_ct.`id`
                LEFT JOIN `chart_status` sp_cs ON sp_pc.`chart_status_id` = sp_cs.`id`
                LEFT JOIN `patient_note` pn ON pn.`id` = (
                    SELECT pnt.`id` 
                    FROM `patient_note` pnt
                    WHERE pnt.`patient_id` = pat.`id` 
                    AND pnt.`type` = 'Specialist'
                    ORDER BY pnt.`created_at`   
                    DESC LIMIT 1
                )
                LEFT JOIN `patient_diagnosis` pd ON pd.`id` = (
					 	SELECT pd1.`id`
					 	FROM `patient_diagnosis` pd1
					 	WHERE (pd1.`is_deleted` = 0 OR pd1.`is_deleted` IS NOT NULL)
					 	AND pd1.`patient_id` = ps.`patient_id`
						AND pd1.end_date IS NULL
						ORDER BY pd1.`start_date` DESC
						LIMIT 1
				)
				LEFT JOIN `diagnosis` d ON pd.`diagnosis_id` = d.`id`
                $sqlWhere
                AND ps.`start_date` IS NOT NULL
            	AND ps.`end_date` IS NULL
                ORDER BY pc.`chart_date` DESC
        ";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $result = $query->fetchAll();
        return $result;
    }

    //REPORT PATIENT PROGRESS
    public function reportPatientProgress($params) {
        $sqlWhere = "WHERE (p.`is_deleted` = 0 OR p.`is_deleted` IS NULL)";
        $sqlWhere2 = "";
        $sqlWhere3 = "";
        $sqlWhereReferral = "";
        $stmtParams = array();
        $stmtParams2 = array();

        if (!is_null($params['date_from']) && $params['date_from'] !== 'NULL' && $params['date_from'] !== 'all' && !empty($params['date_from']) && !is_null($params['date_to']) && $params['date_to'] !== 'null' && $params['date_to'] !== 'all' && !empty($params['date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "p.`referral_date` BETWEEN :date_from AND :date_to";
            $stmtParams['date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
            $stmtParams['date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['date_to'])));

            $sqlWhereReferral .= (empty($sqlWhereReferral) ? "WHERE " : " AND ") . "p.`referral_date` BETWEEN :date_from AND :date_to";
            $stmtParams2['date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
            $stmtParams2['date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['date_to'])));
        }

        if (!is_null($params['receive_date_from']) && $params['receive_date_from'] !== 'NULL' && $params['receive_date_from'] !== 'all' && !empty($params['receive_date_from']) && !is_null($params['receive_date_to']) && $params['receive_date_to'] !== 'null' && $params['receive_date_to'] !== 'all' && !empty($params['receive_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "p.`receive_date` BETWEEN :receive_date_from AND :receive_date_to";
            $stmtParams['receive_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['receive_date_from'])));
            $stmtParams['receive_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['receive_date_to'])));

            $sqlWhereReferral .= (empty($sqlWhereReferral) ? "WHERE " : " AND ") . "p.`referral_date` BETWEEN :date_from AND :date_to";
            $stmtParams2['date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
            $stmtParams2['date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['date_to'])));
        }

        if (!is_null($params['cpap_date_from']) && $params['cpap_date_from'] !== 'NULL' && $params['cpap_date_from'] !== 'all' && !empty($params['cpap_date_from']) && !is_null($params['cpap_date_to']) && $params['cpap_date_to'] !== 'null' && $params['cpap_date_to'] !== 'all' && !empty($params['cpap_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pccs.`chart_date` BETWEEN :cpap_date_from AND :cpap_date_to";
            $stmtParams['cpap_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['cpap_date_from'])));
            $stmtParams['cpap_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['cpap_date_to'])));
        }

        if(isset($params['cpap_status']) && !empty($params['cpap_status']) && 'NULL' !== strtoupper($params['cpap_status']) && 'ALL' !== strtoupper($params['cpap_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "cscs.`description` = :cpap_status";
            $stmtParams['cpap_status'] = $params['cpap_status'];
        }

        if (!is_null($params['cpap_sold_date_from']) && $params['cpap_sold_date_from'] !== 'NULL' && $params['cpap_sold_date_from'] !== 'all' && !empty($params['cpap_sold_date_from']) && !is_null($params['cpap_sold_date_to']) && $params['cpap_sold_date_to'] !== 'null' && $params['cpap_sold_date_to'] !== 'all' && !empty($params['cpap_sold_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pccceee.`chart_date` BETWEEN :cpap_sold_date_from AND :cpap_sold_date_to";
            $stmtParams['cpap_sold_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['cpap_sold_date_from'])));
            $stmtParams['cpap_sold_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['cpap_sold_date_to'])));
        }

        if(isset($params['cpap_sold_status']) && !empty($params['cpap_sold_status']) && 'NULL' !== strtoupper($params['cpap_sold_status']) && 'ALL' !== strtoupper($params['cpap_sold_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "csceee.`description` = :cpap_sold_status";
            $stmtParams['cpap_sold_status'] = $params['cpap_sold_status'];
        }

        if (!is_null($params['hsat_date_from']) && $params['hsat_date_from'] !== 'NULL' && $params['hsat_date_from'] !== 'all' && !empty($params['hsat_date_from']) &&
            !is_null($params['hsat_date_to']) && $params['hsat_date_to'] !== 'null' && $params['hsat_date_to'] !== 'all' && !empty($params['hsat_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pch.`chart_date` BETWEEN :hsat_date_from AND :hsat_date_to";
            $stmtParams['hsat_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['hsat_date_from'])));
            $stmtParams['hsat_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['hsat_date_to'])));
        }

        if(isset($params['hsat_status']) && !empty($params['hsat_status']) && 'NULL' !== strtoupper($params['hsat_status']) && 'ALL' !== strtoupper($params['hsat_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "csh.`description` = :hsat_status";
            $stmtParams['hsat_status'] = $params['hsat_status'];
        }

        if (!is_null($params['hsat_mobile_date_from']) && $params['hsat_mobile_date_from'] !== 'NULL' && $params['hsat_mobile_date_from'] !== 'all' && !empty($params['hsat_mobile_date_from']) &&
            !is_null($params['hsat_mobile_date_to']) && $params['hsat_mobile_date_to'] !== 'null' && $params['hsat_mobile_date_to'] !== 'all' && !empty($params['hsat_mobile_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pc_hsat_mob.`chart_date` BETWEEN :hsat_mobile_date_from AND :hsat_mobile_date_to";
            $stmtParams['hsat_mobile_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['hsat_mobile_date_from'])));
            $stmtParams['hsat_mobile_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['hsat_mobile_date_to'])));
        }

        if(isset($params['hsat_mobile_status']) && !empty($params['hsat_mobile_status']) && 'NULL' !== strtoupper($params['hsat_mobile_status']) && 'ALL' !== strtoupper($params['hsat_mobile_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "cs_hsat_mob.`description` = :hsat_mobile_status";
            $stmtParams['hsat_mobile_status'] = $params['hsat_mobile_status'];
        }

        if (!is_null($params['oximetry_date_from']) && $params['oximetry_date_from'] !== 'NULL' && $params['oximetry_date_from'] !== 'all' && !empty($params['oximetry_date_from']) &&
            !is_null($params['oximetry_date_to']) && $params['oximetry_date_to'] !== 'null' && $params['oximetry_date_to'] !== 'all' && !empty($params['oximetry_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pc_oximetry.`chart_date` BETWEEN :oximetry_date_from AND :oximetry_date_to";
            $stmtParams['oximetry_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['oximetry_date_from'])));
            $stmtParams['oximetry_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['oximetry_date_to'])));
        }

        if(isset($params['oximetry_status']) && !empty($params['oximetry_status']) && 'NULL' !== strtoupper($params['oximetry_status']) && 'ALL' !== strtoupper($params['oximetry_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "cs_oximetry.`description` = :oximetry_status";
            $stmtParams['oximetry_status'] = $params['oximetry_status'];
        }

        if (!is_null($params['hsat_ri_date_from']) && $params['hsat_ri_date_from'] !== 'NULL' && $params['hsat_ri_date_from'] !== 'all' && !empty($params['hsat_ri_date_from']) &&
            !is_null($params['hsat_ri_date_to']) && $params['hsat_ri_date_to'] !== 'null' && $params['hsat_ri_date_to'] !== 'all' && !empty($params['hsat_ri_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pchri.`chart_date` BETWEEN :hsat_ri_date_from AND :hsat_ri_date_to";
            $stmtParams['hsat_ri_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['hsat_ri_date_from'])));
            $stmtParams['hsat_ri_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['hsat_ri_date_to'])));
        }

        if(isset($params['hsat_ri_status']) && !empty($params['hsat_ri_status']) && 'NULL' !== strtoupper($params['hsat_ri_status']) && 'ALL' !== strtoupper($params['hsat_ri_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "cshri.`description` = :hsat_ri_status";
            $stmtParams['hsat_ri_status'] = $params['hsat_ri_status'];
        }

        if (!is_null($params['cpap_eval_date_from']) && $params['cpap_eval_date_from'] !== 'NULL' && $params['cpap_eval_date_from'] !== 'all' && !empty($params['cpap_eval_date_from']) &&
            !is_null($params['cpap_eval_date_to']) && $params['cpap_eval_date_to'] !== 'null' && $params['cpap_eval_date_to'] !== 'all' && !empty($params['cpap_eval_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pcce.`chart_date` BETWEEN :cpap_eval_date_from AND :cpap_eval_date_to";
            $stmtParams['cpap_eval_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['cpap_eval_date_from'])));
            $stmtParams['cpap_eval_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['cpap_eval_date_to'])));
        }

        if(isset($params['cpap_eval_status']) && !empty($params['cpap_eval_status']) && 'NULL' !== strtoupper($params['cpap_eval_status']) && 'ALL' !== strtoupper($params['cpap_eval_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "cscee.`description` = :cpap_eval_status";
            $stmtParams['cpap_eval_status'] = $params['cpap_eval_status'];
        }

        if(isset($params['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf.`company_id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_ors) ";
            }
        }

        if(isset($params['branch']) && !empty($params['branch']) && 'NULL' !== strtoupper($params['branch'])){
            $branches = explode(',' , $params['branch']);
            if(count($branches) > 0){
                $hf_ors = '';
                foreach($branches as $key => $branchId){
                    $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "phf.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

        if(isset($params['billingHealthFacility']) && !empty($params['billingHealthFacility']) && 'NULL' !== strtoupper($params['billingHealthFacility'])){
            $billingBranches = explode(',' , $params['billingHealthFacility']);
            if(count($billingBranches) > 0){
                $bhf_ors = '';
                foreach($billingBranches as $key => $billingBranchId){
                    $bhf_ors .= (empty($bhf_ors) ? '' : ' OR ') . "hfpb.`id` = :billingHealthFacility_id_$key";
                    $stmtParams["billingHealthFacility_id_$key"] = $billingBranchId;
                }
                $sqlWhere .= " AND ($bhf_ors) ";
            }
        }

        if(isset($params['patient']) && !empty($params['patient']) && 'NULL' !== strtoupper($params['patient'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "p.`id` = :patient";
            $stmtParams['patient'] = $params['patient'];
        }

        if(isset($params['billingHealthFacility']) && !empty($params['billingHealthFacility']) && 'NULL' !== strtoupper($params['billingHealthFacility'])){
            $sqlWhere3 .= " AND hf1.`id` = :billingHealthFacility";
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "hfpb.`id` = :billingHealthFacility";
            $stmtParams['billingHealthFacility'] = $params['billingHealthFacility'];
        }

        if(isset($params['chartType']) && !empty($params['chartType']) && 'NULL' !== strtoupper($params['chartType'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pccceeee.`chart_type_id` = :chartType";
            $sqlWhere2 .= " AND pct8.`chart_type_id` = :chartType";
            $stmtParams['chartType'] = $params['chartType'];
        }

        if(isset($params['chartStatus']) && !empty($params['chartStatus']) && 'NULL' !== strtoupper($params['chartStatus'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "csceeee.`description` = :chartStatus";
           // $sqlWhere2 .= " AND css8.`description` = :chartStatus";
            $stmtParams['chartStatus'] = $params['chartStatus'];
        }

        if(isset($params['clinician']) && !empty($params['clinician']) && 'NULL' !== strtoupper($params['clinician'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pch_prov.`id` = :clinician";
            $stmtParams['clinician'] = $params['clinician'];
        }

        if(isset($params['chart_type_cycle']) && !empty($params['chart_type_cycle']) && 'NULL' !== strtoupper($params['chart_type_cycle'])){
            switch ($params['chart_type_cycle']) {
                case 'HSAT':
                    $sqlWhere .= " AND IF (
                                          pch.`id` IS NOT NULL, 
                                          DATEDIFF(pch.`chart_date`, p.`referral_date`) > cth.`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` = 'HSAT' LIMIT 1) DAY) < :now
                                      )";
                    break;
                case 'HSAT Results Interpretation':
                    $sqlWhere .= " AND IF (
                                          pchri.`id` IS NOT NULL, 
                                          DATEDIFF(pchri.`chart_date`, p.`referral_date`) > cthri.`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` = 'HSAT Results Interpretation' LIMIT 1) DAY) < :now
                                      )";
                    break;
                case 'CPAP Setup':
                    $sqlWhere .= " AND IF (
                                          pccs.`id` IS NOT NULL, 
                                          DATEDIFF(pccs.`chart_date`, p.`referral_date`) > ctcs.`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
                                      )";
                    break;
                case 'CPAP Setup Sold':
                    $sqlWhere .= " AND IF (
                                          pccceee.`id` IS NOT NULL, 
                                          DATEDIFF(pccceee.`chart_date`, p.`referral_date`) > ctcss.`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
                                      )";
                    break;
                case 'CPAP Eval':
                    $sqlWhere .= " AND IF (
                                          pcce.`id` IS NOT NULL, 
                                          DATEDIFF(pcce.`chart_date`, p.`referral_date`) > ctce.`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
                                      )";
                    break;
                case 'CPAP End Eval':
                    $sqlWhere .= " AND IF (
                                          pccee.`id` IS NOT NULL, 
                                          DATEDIFF(pccee.`chart_date`, p.`referral_date`) > ctcee.`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
                                      )";
                    break;
                default;
            }

            $stmtParams['now'] = date('Y-m-d 23:59:59');
        }

        if (isset($params['booked']) && !empty($params['booked']) && 'NULL' !== strtoupper($params['booked'])) {
            switch ($params['booked']) {
                case 'HSAT':
                    $sqlWhere .= " AND pch.`chart_date` IS NOT NULL";
                    break;
                case 'HSAT (Mobile)':
                    $sqlWhere .= " AND pc_hsat_mob.`chart_date` IS NOT NULL";
                    break;
                case 'HSAT Results Interpretation':
                    $sqlWhere .= " AND pchri.`chart_date` IS NOT NULL";
                    break;
                case 'CPAP Setup':
                    $sqlWhere .= " AND pccs.`chart_date` IS NOT NULL";
                    break;
                case 'CPAP Eval':
                    $sqlWhere .= " AND pcce.`chart_date` IS NOT NULL";
                    break;
                case 'CPAP End Eval':
                    $sqlWhere .= " AND pccee.`chart_date` IS NOT NULL";
                    break;
                case 'CPAP Setup Sold':
                    $sqlWhere .= " AND pccceee.`chart_date` IS NOT NULL";
                    break;
                case 'Invoices':
                    $sqlWhere .= " AND pb.`billing_date` IS NOT NULL";
                    break;
            }
        }

        if (isset($params['unbooked']) && !empty($params['unbooked']) && 'NULL' !== strtoupper($params['unbooked'])) {
            switch ($params['unbooked']) {
                case 'HSAT':
                    $sqlWhere .= " AND pch.`chart_date` IS NULL";
                    break;
                case 'HSAT (Mobile)':
                    $sqlWhere .= " AND pc_hsat_mob.`chart_date` IS NOT NULL";
                    break;
                case 'HSAT Results Interpretation':
                    $sqlWhere .= " AND pchri.`chart_date` IS NULL";
                    break;
                case 'CPAP Setup':
                    $sqlWhere .= " AND pccs.`chart_date` IS NULL";
                    break;
                case 'CPAP Eval':
                    $sqlWhere .= " AND pcce.`chart_date` IS NULL";
                    break;
                case 'CPAP End Eval':
                    $sqlWhere .= " AND pccee.`chart_date` IS NULL";
                    break;
                case 'CPAP Setup Sold':
                    $sqlWhere .= " AND pccceee.`chart_date` IS NULL";
                    break;
                case 'Invoices':
                    $sqlWhere .= " AND pb.`billing_date` IS NULL";
                    break;
            }
        }

        $sql = "
            SELECT 
              p.`id` AS patientId,
              p.`full_name` AS patientName,
              DATE_FORMAT(p.`referral_date`, '%m/%d/%Y') AS referralDate,
              DATE_FORMAT(p.`receive_date`, '%m/%d/%Y') AS receivedDate,
              temp_ps.`patientServices`,
              prev_hf.`description` AS prevBranchDesc,
              hf.`description` AS branchDesc,
              IF(prov.`full_name` IS NULL, pch_prov.`full_name`, prov.`full_name`) AS primaryProvider,
              cth.`description` AS hsatDesc,
              cthri.`description` AS hsatResultInterDesc,
              ctcs.`description` AS cpapSetupDesc,
              ctcss.`description` AS cpapSetupSoldDesc,
              ctce.`description` AS cpapEvalDesc,
              DATE_FORMAT(pccceee.`chart_date`, '%m/%d/%Y') AS cpapSetupSold,
              ctcee.`description` AS cpapEndEvalDesc,
              csh.`description` AS hsatStatusDesc,
              cshri.`description` AS hsatResultInterStatusDesc,
              cscs.`description` AS cpapSetupStatusDesc,
              csceee.`description` AS cpapSetupSoldStatusDesc,
              csce.`description` AS cpapEvalStatusDesc,
              cscee.`description` AS cpapEndEvalStatusDesc,
              IF(pb.`invoice_no` IS NOT NULL, pb.`invoice_no`, pb.`prev_sys_invoice_no`) AS invoice_no,
              DATE_FORMAT(pch.`chart_date`, '%m/%d/%Y') AS hsatChartDate,
              DATE_FORMAT(pchri.`chart_date`, '%m/%d/%Y') AS hsatResultInterChartDate,
              DATE_FORMAT(pccs.`chart_date`, '%m/%d/%Y') AS cpapSetupChartDate,
              DATE_FORMAT(pcce.`chart_date`, '%m/%d/%Y') AS cpapEvalChartDate,
              DATE_FORMAT(pccee.`chart_date`, '%m/%d/%Y') AS cpapEndEvalChartDate,
              DATE_FORMAT(pb.`billing_date`, '%m/%d/%Y') AS billingDate,
              CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_FORMAT(pch.`chart_date`, '%Y/%m/%d')), ' Day(s)') AS hsatTimeDiff,
              CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_FORMAT(pchri.`chart_date`, '%Y/%m/%d')), ' Day(s)') AS hsatInterTimeDiff,
              CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_FORMAT(pccs.`chart_date`, '%Y/%m/%d')), ' Day(s)') AS cpapSetupTimeDiff,
              CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_FORMAT(pccceee.`chart_date`, '%Y/%m/%d')), ' Day(s)') AS cpapSetupSoldTimeDiff,
              CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_FORMAT(pcce.`chart_date`, '%Y/%m/%d')), ' Day(s)') AS cpapEvalTimeDiff,
              CONCAT(TIMESTAMPDIFF(DAY, DATE_FORMAT(p.`referral_date`, '%Y/%m/%d'), DATE_FORMAT(pccee.`chart_date`, '%Y/%m/%d')), ' Day(s)') AS cpapEndEvalTimeDiff,
              -- DATE_FORMAT(ps.`start_date`, '%m/%d/%Y') AS specialistStartDate,
              ct_hsat_mob.`description` AS ctHsatMobile,
              ct_hsat_repeat.`description` AS ctHsatRepeat,
              ct_hri_tele.`description` AS ctHriRepeat,
              ct_cpap_tele.`description` AS ctCpapTele,
              ct_sixmo_ff_mob.`description` AS ctSixMoFfTele,
              cs_hsat_mob.`description` AS csHsatMobile,
              cs_hsat_repeat.`description` AS csHsatRepeat,
              cs_hri_tele.`description` AS csHriRepeat,
              cs_cpap_tele.`description` AS csCpapTele,
              cs_sixmo_ff_mob.`description` AS csSixMoFfTele,
              DATE_FORMAT(pc_hsat_mob.`chart_date`, '%m/%d/%Y') AS pcHsatMobileDate,
              DATE_FORMAT(pc_hsat_repeat.`chart_date`, '%m/%d/%Y') AS pcHsatRepeatDate,
              DATE_FORMAT(pc_hri_tele.`chart_date`, '%m/%d/%Y') AS pcHriRepeatDate,
              DATE_FORMAT(pc_cpap_tele.`chart_date`, '%m/%d/%Y') AS pcCpapTeleDate,
              DATE_FORMAT(pc_sixmo_ff_mob.`chart_date`, '%m/%d/%Y') AS pcSixMoFfTeleDate,
              sp.`full_name` AS specialistFullName
            FROM `patient` p
            LEFT JOIN (
                SELECT
                    ps.`patient_id`,
                    GROUP_CONCAT(CONCAT_WS('|', ps.`id`, ser.`description`) SEPARATOR ',' ) AS patientServices
                FROM `patient_service` ps
                LEFT JOIN `service` ser ON ps.`service_id` = ser.`id`
                WHERE ps.`is_deleted` = 0
                GROUP BY ps.`patient_id`
            ) temp_ps ON temp_ps.`patient_id` = p.`id`
            LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                      SELECT
                          phf1.`id`
                       FROM `patient_health_facility` phf1
                       WHERE phf1.`end_date` IS NULL
                       AND phf1.`patient_id` = p.`id`
                       ORDER BY phf1.`created_at` DESC
                       LIMIT 1
            )
            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
            LEFT JOIN `patient_health_facility` prev_phf ON prev_phf.`id` = (
                      SELECT
                          prev_phf1.`id`
                       FROM `patient_health_facility` prev_phf1
                       WHERE prev_phf1.`end_date` IS NOT NULL
                       AND prev_phf1.`patient_id` = p.`id`
                       ORDER BY prev_phf1.`created_at` DESC
                       LIMIT 1
            )
            LEFT JOIN `health_facility` prev_hf ON prev_phf.`health_facility_id` = prev_hf.`id`
            LEFT JOIN `company` co ON hf.`company_id` = co.`id`
            LEFT JOIN `patient_chart` pch ON pch.`id` = (
              SELECT 
                pct1.`id` 
              FROM `patient_chart` pct1
              LEFT JOIN `chart_type` ctt1 ON pct1.`chart_type_id` = ctt1.`id`
              LEFT JOIN `chart_status` cs1 ON cs1.`id` = pct1.`chart_status_id`
              WHERE ctt1.`description` = 'HSAT'
              AND pct1.`chart_status_id` IS NOT NULL
              AND pct1.`patient_id` = p.`id`
              AND pct1.`is_deleted` = 0
              ORDER BY pct1.`chart_date` DESC, cs1.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pchprov ON pchprov.`id` = (
              SELECT 
                pctprov.`id` 
              FROM `patient_chart` pctprov
              LEFT JOIN `chart_type` cttprov ON pctprov.`chart_type_id` = cttprov.`id`
              LEFT JOIN `chart_status` csprov ON csprov.`id` = pctprov.`chart_status_id`
              WHERE cttprov.`description` = 'HSAT'
              AND csprov.`description` = 'Completed'
              AND pctprov.`chart_status_id` IS NOT NULL
              AND pctprov.`patient_id` = p.`id`
              AND pctprov.`is_deleted` = 0
              ORDER BY pctprov.`chart_date` DESC, csprov.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `provider` prov ON p.`provider_id` = prov.`id`
            LEFT JOIN `provider` pch_prov ON pchprov.`provider_id` = pch_prov.`id`
            LEFT JOIN `patient_chart` pchri ON pchri.`id` = (
              SELECT 
                pct2.`id` 
              FROM `patient_chart` pct2
              LEFT JOIN `chart_type` ctt2 ON pct2.`chart_type_id` = ctt2.`id`
              LEFT JOIN `chart_status` cs2 ON cs2.`id` = pct2.`chart_status_id`
              WHERE ctt2.`description` = 'HSAT Results Interpretation'
              AND pct2.`chart_status_id` IS NOT NULL
              AND pct2.`patient_id` = p.`id`
              AND pct2.`is_deleted` = 0
              ORDER BY pct2.`chart_date` DESC, cs2.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pccs ON pccs.`id` = (
              SELECT 
                pct3.`id` 
              FROM `patient_chart` pct3
              LEFT JOIN `chart_type` ctt3 ON pct3.`chart_type_id` = ctt3.`id`
              LEFT JOIN `chart_status` cs3 ON cs3.`id` = pct3.`chart_status_id`
              WHERE ctt3.`description` = 'CPAP Setup'
              AND pct3.`chart_status_id` IS NOT NULL
              AND pct3.`patient_id` = p.`id`
              AND pct3.`is_deleted` = 0
              ORDER BY pct3.`chart_date` DESC, cs3.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pcce ON pcce.`id` = (
              SELECT 
                pct4.`id` 
              FROM `patient_chart` pct4
              LEFT JOIN `chart_type` ctt4 ON pct4.`chart_type_id` = ctt4.`id`
              LEFT JOIN `chart_status` cs4 ON cs4.`id` = pct4.`chart_status_id`
              WHERE ctt4.`description` = 'CPAP Eval' 
              AND pct4.`patient_id` = p.`id`
              AND pct4.`chart_status_id` IS NOT NULL
              AND pct4.`is_deleted` = 0
              ORDER BY pct4.`chart_date` DESC, cs4.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pccee ON pccee.`id` = (
              SELECT 
                pct5.`id` 
              FROM `patient_chart` pct5
              LEFT JOIN `chart_type` ctt5 ON pct5.`chart_type_id` = ctt5.`id`
              LEFT JOIN `chart_status` cs5 ON cs5.`id` = pct5.`chart_status_id`
              WHERE ctt5.`description` = 'CPAP End Eval' 
              AND pct5.`patient_id` = p.`id`
              AND pct5.`chart_status_id` IS NOT NULL
              AND pct5.`is_deleted` = 0
              ORDER BY pct5.`chart_date` DESC, cs5.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pccceee ON pccceee.`id` = (
              SELECT 
                pct6.`id`
              FROM `patient_chart` pct6
              LEFT JOIN `chart_type` ctt6 ON pct6.`chart_type_id` = ctt6.`id`
              LEFT JOIN `chart_status` cs6 ON cs6.`id` = pct6.`chart_status_id`
              WHERE ctt6.`description` = 'CPAP Setup Sold'
              AND pct6.`patient_id` = p.`id`
              AND pct6.`chart_status_id` IS NOT NULL
              AND pct6.`is_deleted` = 0
              ORDER BY pct6.`chart_date` DESC, cs6.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pc_hsat_mob ON pc_hsat_mob.`id` = (
              SELECT 
                pct9.`id` 
              FROM `patient_chart` pct9
              LEFT JOIN `chart_type` ctt9 ON pct9.`chart_type_id` = ctt9.`id`
              LEFT JOIN `chart_status` cs9 ON cs9.`id` = pct9.`chart_status_id`
              WHERE ctt9.`description` = 'HSAT (Mobile)' 
              AND pct9.`patient_id` = p.`id`
              AND pct9.`chart_status_id` IS NOT NULL
              AND pct9.`is_deleted` = 0
              ORDER BY pct9.`chart_date` DESC, cs9.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pc_hsat_repeat ON pc_hsat_repeat.`id` = (
              SELECT 
                pct10.`id` 
              FROM `patient_chart` pct10
              LEFT JOIN `chart_type` ctt10 ON pct10.`chart_type_id` = ctt10.`id`
              LEFT JOIN `chart_status` cs10 ON cs10.`id` = pct10.`chart_status_id`
              WHERE ctt10.`description` = 'HSAT Repeat (No Charge)' 
              AND pct10.`patient_id` = p.`id`
              AND pct10.`chart_status_id` IS NOT NULL
              AND pct10.`is_deleted` = 0
              ORDER BY pct10.`chart_date` DESC, cs10.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pc_hri_tele ON pc_hri_tele.`id` = (
              SELECT 
                pct11.`id` 
              FROM `patient_chart` pct11
              LEFT JOIN `chart_type` ctt11 ON pct11.`chart_type_id` = ctt11.`id`
              LEFT JOIN `chart_status` cs11 ON cs11.`id` = pct11.`chart_status_id`
              WHERE ctt11.`description` = 'HSAT Results Interpretation - Telehealth' 
              AND pct11.`patient_id` = p.`id`
              AND pct11.`chart_status_id` IS NOT NULL
              AND pct11.`is_deleted` = 0
              ORDER BY pct11.`chart_date` DESC, cs11.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pc_cpap_tele ON pc_cpap_tele.`id` = (
              SELECT 
                pct12.`id` 
              FROM `patient_chart` pct12
              LEFT JOIN `chart_type` ctt12 ON pct12.`chart_type_id` = ctt12.`id`
              LEFT JOIN `chart_status` cs12 ON cs12.`id` = pct12.`chart_status_id`
              WHERE ctt12.`description` = 'CPAP Setup - Telehealth' 
              AND pct12.`patient_id` = p.`id`
              AND pct12.`chart_status_id` IS NOT NULL
              AND pct12.`is_deleted` = 0
              ORDER BY pct12.`chart_date` DESC, cs12.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pc_sixmo_ff_mob ON pc_sixmo_ff_mob.`id` = (
              SELECT 
                pct13.`id` 
              FROM `patient_chart` pct13
              LEFT JOIN `chart_type` ctt13 ON pct13.`chart_type_id` = ctt13.`id`
              LEFT JOIN `chart_status` cs13 ON cs13.`id` = pct13.`chart_status_id`
              WHERE ctt13.`description` = '6 Month Follow-up (Mobile)' 
              AND pct13.`patient_id` = p.`id`
              AND pct13.`chart_status_id` IS NOT NULL
              AND pct13.`is_deleted` = 0
              ORDER BY pct13.`chart_date` DESC, cs13.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pccceeee ON pccceeee.`id` = (
              SELECT 
                pct8.`id`
              FROM `patient_chart` pct8
              LEFT JOIN `chart_type` ctt8 ON pct8.`chart_type_id` = ctt8.`id`
              LEFT JOIN `chart_status` css8 ON pct8.`chart_status_id` = css8.`id`
              WHERE pct8.`patient_id` = p.`id`
              $sqlWhere2
              AND pct8.`patient_id` = p.`id`
              AND pct8.`chart_status_id` IS NOT NULL
              AND (pct8.`is_deleted` = 0 OR pct8.`is_deleted` IS NULL)
              ORDER BY pct8.`chart_date` DESC, css8.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_billing` pb ON pb.`id` = (
              SELECT 
                pb1.`id`
              FROM `patient_billing` pb1
              LEFT JOIN `patient` pat ON pb1.`patient_id` = pat.`id`
              LEFT JOIN `health_facility` hf ON pb1.`health_facility_id` = hf.`id` 
              LEFT JOIN `patient_insurance` pi ON pb1.`patient_insurance_id` = pi.`id`
              LEFT JOIN `insurance` i ON pi.`insurance_id` = i.`id`
              WHERE pb1.`patient_id` = p.`id`
              AND pb1.`billing_status` != 'Void'
              ORDER BY pb1.`billing_date` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_service` ps ON ps.`id` = (
              SELECT ps1.`id`
              FROM `patient_service` ps1
              WHERE (ps1.`is_deleted` = 0 OR ps1.`is_deleted` IS NOT NULL)
              AND ps1.`patient_id` = p.`id`
              ORDER BY ps1.`start_date` DESC
              LIMIT 1
            )
            LEFT JOIN `specialist` sp ON ps.`specialist_id` = sp.`id` 
            LEFT JOIN `health_facility` hfpb ON pb.`health_facility_id` = hfpb.`id` 
            LEFT JOIN `chart_type` cth ON pch.`chart_type_id` = cth.`id`
            LEFT JOIN `chart_type` cthri ON pchri.`chart_type_id` = cthri.`id`
            LEFT JOIN `chart_type` ctcs ON pccs.`chart_type_id` = ctcs.`id`
            LEFT JOIN `chart_type` ctce ON pcce.`chart_type_id` = ctce.`id`
            LEFT JOIN `chart_type` ctcee ON pccee.`chart_type_id` = ctcee.`id`
            LEFT JOIN `chart_type` ctcss ON pccceee.`chart_type_id` = ctcss.`id`   
           
            LEFT JOIN `chart_type` ct_hsat_mob ON pc_hsat_mob.`chart_type_id` = ct_hsat_mob.`id`
            LEFT JOIN `chart_type` ct_hsat_repeat ON pc_hsat_repeat.`chart_type_id` = ct_hsat_repeat.`id`
            LEFT JOIN `chart_type` ct_hri_tele ON pc_hri_tele.`chart_type_id` = ct_hri_tele.`id`
            LEFT JOIN `chart_type` ct_cpap_tele ON pc_cpap_tele.`chart_type_id` = ct_cpap_tele.`id`
            LEFT JOIN `chart_type` ct_sixmo_ff_mob ON pc_sixmo_ff_mob.`chart_type_id` = ct_sixmo_ff_mob.`id`
            
            LEFT JOIN `chart_status` csh ON pch.`chart_status_id` = csh.`id`
            LEFT JOIN `chart_status` cshri ON pchri.`chart_status_id` = cshri.`id`
            LEFT JOIN `chart_status` cscs ON pccs.`chart_status_id` = cscs.`id`
            LEFT JOIN `chart_status` csce ON pcce.`chart_status_id` = csce.`id`
            LEFT JOIN `chart_status` cscee ON pccee.`chart_status_id` = cscee.`id`
            LEFT JOIN `chart_status` csceee ON pccceee.`chart_status_id` = csceee.`id`
            LEFT JOIN `chart_status` csceeee ON pccceeee.`chart_status_id` = csceeee.`id`
            
            LEFT JOIN `chart_status` cs_hsat_mob ON pc_hsat_mob.`chart_status_id` = cs_hsat_mob.`id`
            LEFT JOIN `chart_status` cs_hsat_repeat ON pc_hsat_repeat.`chart_status_id` = cs_hsat_repeat.`id`
            LEFT JOIN `chart_status` cs_hri_tele ON pc_hri_tele.`chart_status_id` = cs_hri_tele.`id`
            LEFT JOIN `chart_status` cs_cpap_tele ON pc_cpap_tele.`chart_status_id` = cs_cpap_tele.`id`
            LEFT JOIN `chart_status` cs_sixmo_ff_mob ON pc_sixmo_ff_mob.`chart_status_id` = cs_sixmo_ff_mob.`id`
            
            $sqlWhere
            AND p.`is_duplicate` = 0
            AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
            AND p.`full_name` IS NOT NULL
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf.`description` IS NOT NULL
            AND p.`full_name` IS NOT NULL
            AND p.`referral_date` IS NOT NULL
            AND p.`is_test_patient` = 0
            AND p.`referral_date` != '0000-00-00 00:00:00'
            ORDER BY p.`referral_date`, pch.`chart_date`, pchri.`chart_date`, pccs.`chart_date`, pcce.`chart_date`, pccee.`chart_date`
        ";

        $sql2 = "SELECT 
                     COUNT(DISTINCT p.`id`) AS referralCount
                 FROM `patient` p
                 LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                      SELECT
                          phf1.`id`
                       FROM `patient_health_facility` phf1
                       WHERE phf1.`end_date` IS NULL
                       AND phf1.`patient_id` = p.`id`
                       ORDER BY phf1.`created_at` DESC
                       LIMIT 1
                  )
                 LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                 $sqlWhereReferral
                 AND p.`is_deleted` = 0
                 AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
                 AND p.`is_test_patient` = 0
                 AND p.`is_duplicate` = 0
                 AND hf.`description` IS NOT NULL
                 AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')";

        $chartTypes = $this->getEntityManager()->getConnection()->executeQuery("
            SELECT 
                ct.`id` AS chart_type_id,
                ct.`description`
            FROM `chart_type` ct    
            WHERE (ct.`is_deleted` = 0 OR ct.`is_deleted` IS NOT NULL)
        ");
        $chartTypes = $chartTypes->fetchAll();

        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $query2 = $this->getEntityManager()->getConnection()->prepare($sql2);
        $query2->execute($stmtParams2);

        $result['patientProgress'] = $query->fetchAll();
        $result['count'] = $query2->fetch();

        $result['summary'] = array(
            'hsat' => array(),
            'hsatMobile' => array(),
            'hri' => array(),
            'cpapSetup' => array(),
            'cpapSetupSold' => array(),
            'cpapEval' => array(),
            'cpapEndEval' => array(),
        );

        foreach ($result['patientProgress'] as $row) {
            if($row['hsatDesc'] == 'HSAT') {
                if(isset($result['summary']['hsat'][$row['hsatStatusDesc']])) {
                    $result['summary']['hsat'][$row['hsatStatusDesc']]++;
                } else {
                    $result['summary']['hsat'][$row['hsatStatusDesc']] = 1;
                }
            }

            if($row['ctHsatMobile'] == 'HSAT (Mobile)') {
                if(isset($result['summary']['hsatMobile'][$row['csHsatMobile']])) {
                    $result['summary']['hsatMobile'][$row['csHsatMobile']]++;
                } else {
                    $result['summary']['hsatMobile'][$row['csHsatMobile']] = 1;
                }
            }

            if($row['hsatResultInterDesc'] == 'HSAT Results Interpretation') {
                if(isset($result['summary']['hri'][$row['hsatResultInterStatusDesc']])) {
                    $result['summary']['hri'][$row['hsatResultInterStatusDesc']]++;
                } else {
                    $result['summary']['hri'][$row['hsatResultInterStatusDesc']] = 1;
                }
            }

            if($row['cpapSetupDesc'] == 'CPAP Setup') {
                if(isset($result['summary']['cpapSetup'][$row['cpapSetupStatusDesc']])) {
                    $result['summary']['cpapSetup'][$row['cpapSetupStatusDesc']]++;
                } else {
                    $result['summary']['cpapSetup'][$row['cpapSetupStatusDesc']] = 1;
                }
            }

            if($row['cpapSetupSoldDesc'] == 'CPAP Setup Sold') {
                if(isset($result['summary']['cpapSetupSold'][$row['cpapSetupSoldStatusDesc']])) {
                    $result['summary']['cpapSetupSold'][$row['cpapSetupSoldStatusDesc']]++;
                } else {
                    $result['summary']['cpapSetupSold'][$row['cpapSetupSoldStatusDesc']] = 1;
                }
            }

            if($row['cpapEvalDesc'] == 'CPAP Eval') {
                if(isset($result['summary']['cpapEval'][$row['cpapEvalStatusDesc']])) {
                    $result['summary']['cpapEval'][$row['cpapEvalStatusDesc']]++;
                } else {
                    $result['summary']['cpapEval'][$row['cpapEvalStatusDesc']] = 1;
                }
            }

            if($row['cpapEndEvalDesc'] == 'CPAP End Eval') {
                if(isset($result['summary']['cpapEndEval'][$row['cpapEndEvalStatusDesc']])) {
                    $result['summary']['cpapEndEval'][$row['cpapEndEvalStatusDesc']]++;
                } else {
                    $result['summary']['cpapEndEval'][$row['cpapEndEvalStatusDesc']] = 1;
                }
            }
        }


//        foreach ($result['patientProgress'] as $row) {
//            foreach($chartTypes as $chartType) {
//                if((!empty($row['hsatChartDate']) && date_diff(new \DateTime($row['referralDate']), new \DateTime($row['hsatChartDate']))->format('%a') > $chartType['expected_no_of_days']) ||
//                    (empty($row['hsatChartDate']) && date_diff(new \DateTime($row['referralDate']), new \DateTime())->format('%a') > $chartType['expected_no_of_days'])) {
//                    if (isset($result['summary']['overdue'][$chartType['description']])) {
//                        $result['summary']['overdue'][$chartType['description']]++;
//                    } else {
//                        $result['summary']['overdue'][$chartType['description']] = 1;
//                    }
//                }
//
////                if((!empty($row['hsatResultInterChartDate']) && date_diff(new \DateTime($row['referralDate']), new \DateTime($row['hsatResultInterChartDate']))->format('%a') > $chartType['expected_no_of_days']) ||
////                    (empty($row['hsatResultInterChartDate']) && date_diff(new \DateTime($row['referralDate']), new \DateTime())->format('%a') > $chartType['expected_no_of_days'])) {
////                    if (isset($result['summary']['overdue'][$chartType['description']])) {
////                        $result['summary']['overdue'][$chartType['description']]++;
////                    } else {
////                        $result['summary']['overdue'][$chartType['description']] = 1;
////                    }
////                }
//            }
//        }

        return $result;
    }

    public function reportPatientProgressSummary($params) {
        $sqlWhere = "WHERE (p.`is_deleted` = 0 OR p.`is_deleted` IS NULL)";
        $sqlWhere2 = "";
        $sqlWhere3 = "";
        $stmtParams = array();

        if (!is_null($params['date_from']) && $params['date_from'] !== 'NULL' && $params['date_from'] !== 'all' && !empty($params['date_from']) && !is_null($params['date_to']) && $params['date_to'] !== 'null' && $params['date_to'] !== 'all' && !empty($params['date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "p.`referral_date` BETWEEN :date_from AND :date_to";
            $stmtParams['date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
            $stmtParams['date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['date_to'])));
        }

        if (!is_null($params['receive_date_from']) && $params['receive_date_from'] !== 'NULL' && $params['receive_date_from'] !== 'all' && !empty($params['receive_date_from']) && !is_null($params['receive_date_to']) && $params['receive_date_to'] !== 'null' && $params['receive_date_to'] !== 'all' && !empty($params['receive_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "p.`receive_date` BETWEEN :receive_date_from AND :receive_date_to";
            $stmtParams['receive_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['receive_date_from'])));
            $stmtParams['receive_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['receive_date_to'])));
        }

        if (!is_null($params['cpap_date_from']) && $params['cpap_date_from'] !== 'NULL' && $params['cpap_date_from'] !== 'all' && !empty($params['cpap_date_from']) && !is_null($params['cpap_date_to']) && $params['cpap_date_to'] !== 'null' && $params['cpap_date_to'] !== 'all' && !empty($params['cpap_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pccs.`chart_date` BETWEEN :cpap_date_from AND :cpap_date_to";
            $stmtParams['cpap_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['cpap_date_from'])));
            $stmtParams['cpap_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['cpap_date_to'])));
        }

        if(isset($params['cpap_status']) && !empty($params['cpap_status']) && 'NULL' !== strtoupper($params['cpap_status']) && 'ALL' !== strtoupper($params['cpap_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "cscs.`description` = :cpap_status";
            $stmtParams['cpap_status'] = $params['cpap_status'];
        }

        if (!is_null($params['cpap_sold_date_from']) && $params['cpap_sold_date_from'] !== 'NULL' && $params['cpap_sold_date_from'] !== 'all' && !empty($params['cpap_sold_date_from']) && !is_null($params['cpap_sold_date_to']) && $params['cpap_sold_date_to'] !== 'null' && $params['cpap_sold_date_to'] !== 'all' && !empty($params['cpap_sold_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pccceee.`chart_date` BETWEEN :cpap_sold_date_from AND :cpap_sold_date_to";
            $stmtParams['cpap_sold_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['cpap_sold_date_from'])));
            $stmtParams['cpap_sold_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['cpap_sold_date_to'])));
        }

        if(isset($params['cpap_sold_status']) && !empty($params['cpap_sold_status']) && 'NULL' !== strtoupper($params['cpap_sold_status']) && 'ALL' !== strtoupper($params['cpap_sold_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "csceee.`description` = :cpap_sold_status";
            $stmtParams['cpap_sold_status'] = $params['cpap_sold_status'];
        }

        if (!is_null($params['hsat_date_from']) && $params['hsat_date_from'] !== 'NULL' && $params['hsat_date_from'] !== 'all' && !empty($params['hsat_date_from']) &&
            !is_null($params['hsat_date_to']) && $params['hsat_date_to'] !== 'null' && $params['hsat_date_to'] !== 'all' && !empty($params['hsat_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pch.`chart_date` BETWEEN :hsat_date_from AND :hsat_date_to";
            $stmtParams['hsat_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['hsat_date_from'])));
            $stmtParams['hsat_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['hsat_date_to'])));
        }

        if(isset($params['hsat_status']) && !empty($params['hsat_status']) && 'NULL' !== strtoupper($params['hsat_status']) && 'ALL' !== strtoupper($params['hsat_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "csh.`description` = :hsat_status";
            $stmtParams['hsat_status'] = $params['hsat_status'];
        }

        if (!is_null($params['hsat_mobile_date_from']) && $params['hsat_mobile_date_from'] !== 'NULL' && $params['hsat_mobile_date_from'] !== 'all' && !empty($params['hsat_mobile_date_from']) &&
            !is_null($params['hsat_mobile_date_to']) && $params['hsat_mobile_date_to'] !== 'null' && $params['hsat_mobile_date_to'] !== 'all' && !empty($params['hsat_mobile_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pc_hsat_mob.`chart_date` BETWEEN :hsat_mobile_date_from AND :hsat_mobile_date_to";
            $stmtParams['hsat_mobile_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['hsat_mobile_date_from'])));
            $stmtParams['hsat_mobile_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['hsat_mobile_date_to'])));
        }

        if(isset($params['hsat_mobile_status']) && !empty($params['hsat_mobile_status']) && 'NULL' !== strtoupper($params['hsat_mobile_status']) && 'ALL' !== strtoupper($params['hsat_mobile_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "cs_hsat_mob.`description` = :hsat_mobile_status";
            $stmtParams['hsat_mobile_status'] = $params['hsat_mobile_status'];
        }

        if (!is_null($params['oximetry_date_from']) && $params['oximetry_date_from'] !== 'NULL' && $params['oximetry_date_from'] !== 'all' && !empty($params['oximetry_date_from']) &&
            !is_null($params['oximetry_date_to']) && $params['oximetry_date_to'] !== 'null' && $params['oximetry_date_to'] !== 'all' && !empty($params['oximetry_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pc_oximetry.`chart_date` BETWEEN :oximetry_date_from AND :oximetry_date_to";
            $stmtParams['oximetry_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['oximetry_date_from'])));
            $stmtParams['oximetry_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['oximetry_date_to'])));
        }

        if(isset($params['oximetry_status']) && !empty($params['oximetry_status']) && 'NULL' !== strtoupper($params['oximetry_status']) && 'ALL' !== strtoupper($params['oximetry_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "cs_oximetry.`description` = :oximetry_status";
            $stmtParams['oximetry_status'] = $params['oximetry_status'];
        }

        if (!is_null($params['hsat_ri_date_from']) && $params['hsat_ri_date_from'] !== 'NULL' && $params['hsat_ri_date_from'] !== 'all' && !empty($params['hsat_ri_date_from']) &&
            !is_null($params['hsat_ri_date_to']) && $params['hsat_ri_date_to'] !== 'null' && $params['hsat_ri_date_to'] !== 'all' && !empty($params['hsat_ri_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pchri.`chart_date` BETWEEN :hsat_ri_date_from AND :hsat_ri_date_to";
            $stmtParams['hsat_ri_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['hsat_ri_date_from'])));
            $stmtParams['hsat_ri_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['hsat_ri_date_to'])));
        }

        if(isset($params['hsat_ri_status']) && !empty($params['hsat_ri_status']) && 'NULL' !== strtoupper($params['hsat_ri_status']) && 'ALL' !== strtoupper($params['hsat_ri_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "cshri.`description` = :hsat_ri_status";
            $stmtParams['hsat_ri_status'] = $params['hsat_ri_status'];
        }

        if (!is_null($params['cpap_eval_date_from']) && $params['cpap_eval_date_from'] !== 'NULL' && $params['cpap_eval_date_from'] !== 'all' && !empty($params['cpap_eval_date_from']) &&
            !is_null($params['cpap_eval_date_to']) && $params['cpap_eval_date_to'] !== 'null' && $params['cpap_eval_date_to'] !== 'all' && !empty($params['cpap_eval_date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pcce.`chart_date` BETWEEN :cpap_eval_date_from AND :cpap_eval_date_to";
            $stmtParams['cpap_eval_date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['cpap_eval_date_from'])));
            $stmtParams['cpap_eval_date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['cpap_eval_date_to'])));
        }

        if(isset($params['cpap_eval_status']) && !empty($params['cpap_eval_status']) && 'NULL' !== strtoupper($params['cpap_eval_status']) && 'ALL' !== strtoupper($params['cpap_eval_status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "cscee.`description` = :cpap_eval_status";
            $stmtParams['cpap_eval_status'] = $params['cpap_eval_status'];
        }

        if(isset($params['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf.`company_id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_ors) ";
            }
        }

        if(isset($params['branch']) && !empty($params['branch']) && 'NULL' !== strtoupper($params['branch'])){
            $branches = explode(',' , $params['branch']);
            if(count($branches) > 0){
                $hf_ors = '';
                foreach($branches as $key => $branchId){
                    $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "phf.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

        if(isset($params['billingHealthFacility']) && !empty($params['billingHealthFacility']) && 'NULL' !== strtoupper($params['billingHealthFacility'])){
            $billingBranches = explode(',' , $params['billingHealthFacility']);
            if(count($billingBranches) > 0){
                $bhf_ors = '';
                foreach($billingBranches as $key => $billingBranchId){
                    $bhf_ors .= (empty($bhf_ors) ? '' : ' OR ') . "hfpb.`id` = :billingHealthFacility_id_$key";
                    $stmtParams["billingHealthFacility_id_$key"] = $billingBranchId;
                }
                $sqlWhere .= " AND ($bhf_ors) ";
            }
        }

        if(isset($params['patient']) && !empty($params['patient']) && 'NULL' !== strtoupper($params['patient'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "p.`id` = :patient";
            $stmtParams['patient'] = $params['patient'];
        }

        if(isset($params['billingHealthFacility']) && !empty($params['billingHealthFacility']) && 'NULL' !== strtoupper($params['billingHealthFacility'])){
            $sqlWhere3 .= " AND hf1.`id` = :billingHealthFacility";
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "hfpb.`id` = :billingHealthFacility";
            $stmtParams['billingHealthFacility'] = $params['billingHealthFacility'];
        }

        if(isset($params['chartType']) && !empty($params['chartType']) && 'NULL' !== strtoupper($params['chartType'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pccceeee.`chart_type_id` = :chartType";
            $sqlWhere2 .= " AND pct8.`chart_type_id` = :chartType";
            $stmtParams['chartType'] = $params['chartType'];
        }

        if(isset($params['chartStatus']) && !empty($params['chartStatus']) && 'NULL' !== strtoupper($params['chartStatus'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "csceeee.`description` = :chartStatus";
            // $sqlWhere2 .= " AND css8.`description` = :chartStatus";
            $stmtParams['chartStatus'] = $params['chartStatus'];
        }

        if(isset($params['clinician']) && !empty($params['clinician']) && 'NULL' !== strtoupper($params['clinician'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pch_prov.`id` = :clinician";
            $stmtParams['clinician'] = $params['clinician'];
        }

        if(isset($params['chart_type_cycle']) && !empty($params['chart_type_cycle']) && 'NULL' !== strtoupper($params['chart_type_cycle'])){
            switch ($params['chart_type_cycle']) {
                case 'HSAT':
                    $sqlWhere .= " AND IF (
                                          pch.id IS NOT NULL, 
                                          DATEDIFF(pch.`chart_date`, p.`referral_date`) > cth.`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 = 'HSAT' LIMIT 1) DAY) < :now
                                      )";
                    break;
                case 'HSAT (Mobile)':
                    $sqlWhere .= " AND IF (
                                          pchm.id IS NOT NULL, 
                                          DATEDIFF(pc_hsat_mob.`chart_date`, p.`referral_date`) > cth.`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 = 'HSAT (Mobile)' LIMIT 1) DAY) < :now
                                      )";
                    break;
                case 'HSAT Results Interpretation':
                    $sqlWhere .= " AND IF (
                                          pchri.id IS NOT NULL, 
                                          DATEDIFF(pchri.`chart_date`, p.`referral_date`) > cthri.`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 = 'HSAT Results Interpretation' LIMIT 1) DAY) < :now
                                      )";
                    break;
                case 'CPAP Setup':
                    $sqlWhere .= " AND IF (
                                          pccs.id IS NOT NULL, 
                                          DATEDIFF(pccs.`chart_date`, p.`referral_date`) > ctcs.`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
                                      )";
                    break;
                case 'CPAP Setup Sold':
                    $sqlWhere .= " AND IF (
                                          pccceee.id IS NOT NULL, 
                                          DATEDIFF(pccceee.`chart_date`, p.`referral_date`) > ctcss.`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
                                      )";
                    break;
                case 'CPAP Eval':
                    $sqlWhere .= " AND IF (
                                          pcce.id IS NOT NULL, 
                                          DATEDIFF(pcce.`chart_date`, p.`referral_date`) > ctce.`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
                                      )";
                    break;
                case 'CPAP End Eval':
                    $sqlWhere .= " AND IF (
                                          pccee.id IS NOT NULL, 
                                          DATEDIFF(pccee.`chart_date`, p.`referral_date`) > ctcee.`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
                                      )";
                    break;
                default;
            }

            $stmtParams['now'] = date('Y-m-d 23:59:59');
        }

        if (isset($params['booked']) && !empty($params['booked']) && 'NULL' !== strtoupper($params['booked'])) {
            switch ($params['booked']) {
                case 'HSAT':
                    $sqlWhere .= " AND pch.`chart_date` IS NOT NULL";
                    break;
                case 'HSAT (Mobile)':
                    $sqlWhere .= " AND pc_hsat_mob.`chart_date` IS NOT NULL";
                    break;
                case 'HSAT Results Interpretation':
                    $sqlWhere .= " AND pchri.`chart_date` IS NOT NULL";
                    break;
                case 'CPAP Setup':
                    $sqlWhere .= " AND pccs.`chart_date` IS NOT NULL";
                    break;
                case 'CPAP Eval':
                    $sqlWhere .= " AND pcce.`chart_date` IS NOT NULL";
                    break;
                case 'CPAP End Eval':
                    $sqlWhere .= " AND pccee.`chart_date` IS NOT NULL";
                    break;
                case 'CPAP Setup Sold':
                    $sqlWhere .= " AND pccceee.`chart_date` IS NOT NULL";
                    break;
                case 'Invoices':
                    $sqlWhere .= " AND pb.`billing_date` IS NOT NULL";
                    break;
            }
        }

        if (isset($params['unbooked']) && !empty($params['unbooked']) && 'NULL' !== strtoupper($params['unbooked'])) {
            switch ($params['unbooked']) {
                case 'HSAT':
                    $sqlWhere .= " AND pch.`chart_date` IS NULL";
                    break;
                case 'HSAT (Mobile)':
                    $sqlWhere .= " AND pc_hsat_mob.`chart_date` IS NULL";
                    break;
                case 'HSAT Results Interpretation':
                    $sqlWhere .= " AND pchri.`chart_date` IS NULL";
                    break;
                case 'CPAP Setup':
                    $sqlWhere .= " AND pccs.`chart_date` IS NULL";
                    break;
                case 'CPAP Eval':
                    $sqlWhere .= " AND pcce.`chart_date` IS NULL";
                    break;
                case 'CPAP End Eval':
                    $sqlWhere .= " AND pccee.`chart_date` IS NULL";
                    break;
                case 'CPAP Setup Sold':
                    $sqlWhere .= " AND pccceee.`chart_date` IS NULL";
                    break;
                case 'Invoices':
                    $sqlWhere .= " AND pb.`billing_date` IS NULL";
                    break;
            }
        }

        $sql = "
            SELECT 
               COUNT(DISTINCT p.`id`) AS referralCount,
               SUM(IF(pch.`chart_date` IS NOT NULL, 1, 0)) AS hsatCount,
               SUM(IF(pc_hsat_mob.`chart_date` IS NOT NULL, 1, 0)) AS hsatMobileCount,
               SUM(IF(pchri.`chart_date` IS NOT NULL, 1, 0)) AS hsatResultInterCount,
               SUM(IF(pccs.`chart_date` IS NOT NULL, 1, 0)) AS cpapSetupCount,
               SUM(IF(pccceee.`chart_date` IS NOT NULL, 1, 0)) AS cpapSetupSoldCount,
               SUM(IF(pcce.`chart_date` IS NOT NULL, 1, 0)) AS cpapEvalCount,
               SUM(IF(pccee.`chart_date` IS NOT NULL, 1, 0)) AS cpapEndEvalCount,
               SUM(IF(pb.`billing_date` IS NOT NULL, 1, 0)) AS invoicesCount
            FROM `patient` p
            LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                      SELECT
                          phf1.`id`
                       FROM `patient_health_facility` phf1
                       WHERE phf1.`end_date` IS NULL
                       AND phf1.`patient_id` = p.`id`
                       ORDER BY phf1.`created_at` DESC
                       LIMIT 1
            )
            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
            LEFT JOIN `company` co ON hf.`company_id` = co.`id`
            LEFT JOIN `patient_chart` pch ON pch.`id` = (
              SELECT 
                pct1.`id` 
              FROM `patient_chart` pct1
              LEFT JOIN `chart_type` ctt1 ON pct1.`chart_type_id` = ctt1.`id`
              LEFT JOIN `chart_status` cs1 ON cs1.`chart_type_id` = ctt1.`id`
              LEFT JOIN `chart_status` cs_ordinal1 ON cs_ordinal1.`id` = pct1.`chart_status_id`
              WHERE ctt1.`description` = 'HSAT'
              AND cs1.`description` IN('Booked', 'Completed')
              AND pct1.`chart_status_id` IS NOT NULL
              AND pct1.`patient_id` = p.`id`
              AND pct1.`is_deleted` = 0
              ORDER BY pct1.`chart_date` DESC, cs_ordinal1.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pchprov ON pchprov.`id` = (
              SELECT 
                pctprov.`id` 
              FROM `patient_chart` pctprov
              LEFT JOIN `chart_type` cttprov ON pctprov.`chart_type_id` = cttprov.`id`
              LEFT JOIN `chart_status` csprov ON csprov.`id` = pctprov.`chart_status_id`
              WHERE cttprov.`description` = 'HSAT'
              AND csprov.`description` = 'Completed'
              AND pctprov.`chart_status_id` IS NOT NULL
              AND pctprov.`patient_id` = p.`id`
              AND pctprov.`is_deleted` = 0
              ORDER BY pctprov.`chart_date` DESC, csprov.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `provider` prov ON p.`provider_id` = prov.`id`
            LEFT JOIN `provider` pch_prov ON pchprov.`provider_id` = pch_prov.`id`
            LEFT JOIN `patient_chart` pchri ON pchri.`id` = (
              SELECT 
                pct2.`id` 
              FROM `patient_chart` pct2
              LEFT JOIN `chart_type` ctt2 ON pct2.`chart_type_id` = ctt2.`id`
              LEFT JOIN `chart_status` cs2 ON cs2.`chart_type_id` = ctt2.`id`
              LEFT JOIN `chart_status` cs_ordinal2 ON cs_ordinal2.`id` = pct2.`chart_status_id`
              WHERE ctt2.`description` = 'HSAT Results Interpretation'
              AND cs2.`description` IN('Booked', 'Completed')
              AND pct2.`chart_status_id` IS NOT NULL
              AND pct2.`patient_id` = p.`id`
              AND pct2.`is_deleted` = 0
              ORDER BY pct2.`chart_date` DESC, cs_ordinal2.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pccs ON pccs.`id` = (
              SELECT 
                pct3.`id` 
              FROM `patient_chart` pct3
              LEFT JOIN `chart_type` ctt3 ON pct3.`chart_type_id` = ctt3.`id`
              LEFT JOIN `chart_status` cs3 ON cs3.`chart_type_id` = ctt3.`id`
              LEFT JOIN `chart_status` cs_ordinal3 ON cs_ordinal3.`id` = pct3.`chart_status_id`
              WHERE ctt3.`description` = 'CPAP Setup'
              AND cs3.`description` IN('Booked', 'Completed')
              AND pct3.`chart_status_id` IS NOT NULL
              AND pct3.`patient_id` = p.`id`
              AND pct3.`is_deleted` = 0
              ORDER BY pct3.`chart_date` DESC, cs_ordinal3.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pcce ON pcce.`id` = (
              SELECT 
                pct4.`id` 
              FROM `patient_chart` pct4
              LEFT JOIN `chart_type` ctt4 ON pct4.`chart_type_id` = ctt4.`id`
              LEFT JOIN `chart_status` cs4 ON cs4.`chart_type_id` = ctt4.`id`
              LEFT JOIN `chart_status` cs_ordinal4 ON cs_ordinal4.`id` = pct4.`chart_status_id`
              WHERE ctt4.`description` = 'CPAP Eval'
              AND cs4.`description` IN('Booked', 'Completed')
              AND pct4.`chart_status_id` IS NOT NULL
              AND pct4.`patient_id` = p.`id`
              AND pct4.`is_deleted` = 0
              ORDER BY pct4.`chart_date` DESC, cs_ordinal4.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pccee ON pccee.`id` = (
              SELECT 
                pct5.`id` 
              FROM `patient_chart` pct5
              LEFT JOIN `chart_type` ctt5 ON pct5.`chart_type_id` = ctt5.`id`
              LEFT JOIN `chart_status` cs5 ON cs5.`chart_type_id` = ctt5.`id`
              LEFT JOIN `chart_status` cs_ordinal5 ON cs_ordinal5.`id` = pct5.`chart_status_id`
              WHERE ctt5.`description` = 'CPAP End Eval'
              AND cs5.`description` IN('Booked', 'Completed')
              AND pct5.`chart_status_id` IS NOT NULL
              AND pct5.`patient_id` = p.`id`
              AND pct5.`is_deleted` = 0
              ORDER BY pct5.`chart_date` DESC, cs_ordinal5.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pccceee ON pccceee.`id` = (
              SELECT 
                pct6.`id`
              FROM `patient_chart` pct6
              LEFT JOIN `chart_type` ctt6 ON pct6.`chart_type_id` = ctt6.`id`
              LEFT JOIN `chart_status` cs6 ON cs6.`chart_type_id` = ctt6.`id`
              LEFT JOIN `chart_status` cs_ordinal6 ON cs_ordinal6.`id` = pct6.`chart_status_id`
              WHERE ctt6.`description` = 'CPAP Setup Sold'
              AND cs6.`description` IN('Booked', 'Completed')
              AND pct6.`chart_status_id` IS NOT NULL
              AND pct6.`patient_id` = p.`id`
              AND pct6.`is_deleted` = 0
              ORDER BY pct6.`chart_date` DESC, cs_ordinal6.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pc_oximetry ON pc_oximetry.`id` = (
              SELECT 
                pct7.`id`
              FROM `patient_chart` pct7
              LEFT JOIN `chart_type` ctt7 ON pct7.`chart_type_id` = ctt7.`id`
              LEFT JOIN `chart_status` cs7 ON cs7.`chart_type_id` = ctt7.`id`
              WHERE ctt7.`description` = 'Oximetry'
              AND cs7.`description` IN('Booked', 'Completed')
              AND pct7.`chart_status_id` IS NOT NULL
              AND pct7.`patient_id` = p.`id`
              AND pct7.`is_deleted` = 0
              ORDER BY pct7.`chart_date` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pchm ON pchm.`id` = (
              SELECT 
                pct8.`id` 
              FROM `patient_chart` pct8
              LEFT JOIN `chart_type` ctt8 ON pct8.`chart_type_id` = ctt8.`id`
              LEFT JOIN `chart_status` cs8 ON cs8.`chart_type_id` = ctt8.`id`
              LEFT JOIN `chart_status` cs_ordinal8 ON cs_ordinal8.`id` = pct8.`chart_status_id`
              WHERE ctt8.`description` = 'HSAT (Mobile)'
              AND cs8.`description` IN('Booked', 'Completed')
              AND pct8.`chart_status_id` IS NOT NULL
              AND pct8.`patient_id` = p.`id`
              AND pct8.`is_deleted` = 0
              ORDER BY pct8.`chart_date` DESC, cs_ordinal8.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pc_hsat_mob ON pc_hsat_mob.`id` = (
              SELECT 
                pct9.`id` 
              FROM `patient_chart` pct9
              LEFT JOIN `chart_type` ctt9 ON pct9.`chart_type_id` = ctt9.`id`
              LEFT JOIN `chart_status` cs9 ON cs9.`id` = pct9.`chart_status_id`
              WHERE ctt9.`description` = 'HSAT (Mobile)' 
              AND pct9.`patient_id` = p.`id`
              AND pct9.`chart_status_id` IS NOT NULL
              AND pct9.`is_deleted` = 0
              ORDER BY pct9.`chart_date` DESC, cs9.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pccceeee ON pccceeee.`id` = (
              SELECT 
                pct8.`id`
              FROM `patient_chart` pct8
              LEFT JOIN `chart_type` ctt8 ON pct8.`chart_type_id` = ctt8.`id`
              LEFT JOIN `chart_status` css8 ON pct8.`chart_status_id` = css8.`id`
              LEFT JOIN `chart_status` css_ordinal ON pct8.`chart_status_id` = css_ordinal.`id`
              WHERE pct8.`patient_id` = p.`id`
              $sqlWhere2
              AND pct8.`patient_id` = p.`id`
              AND pct8.`chart_status_id` IS NOT NULL
              AND (pct8.`is_deleted` = 0 OR pct8.`is_deleted` IS NULL)
              ORDER BY pct8.`chart_date` DESC, css_ordinal.ordinal DESC
              LIMIT 1
            )
            LEFT JOIN `patient_billing` pb ON pb.`id` = (
              SELECT 
                pb1.`id`
              FROM `patient_billing` pb1
              LEFT JOIN `patient` pat ON pb1.`patient_id` = pat.`id`
              LEFT JOIN `health_facility` hf ON pb1.`health_facility_id` = hf.`id` 
              LEFT JOIN `patient_insurance` pi ON pb1.`patient_insurance_id` = pi.`id`
              LEFT JOIN `insurance` i ON pi.`insurance_id` = i.`id`
              WHERE pb1.`patient_id` = p.`id`
              AND pb1.`billing_status` != 'Void'
              ORDER BY pb1.`billing_date` DESC
              LIMIT 1
            )
            LEFT JOIN `health_facility` hfpb ON pb.`health_facility_id` = hfpb.`id` 
            LEFT JOIN `chart_type` cth ON pch.`chart_type_id` = cth.`id`
            LEFT JOIN `chart_type` cthri ON pchri.`chart_type_id` = cthri.`id`
            LEFT JOIN `chart_type` ctcs ON pccs.`chart_type_id` = ctcs.`id`
            LEFT JOIN `chart_type` ctcss ON pccceee.`chart_type_id` = ctcss.`id`
            LEFT JOIN `chart_type` ctce ON pcce.`chart_type_id` = ctce.`id`
            LEFT JOIN `chart_type` ctcee ON pccee.`chart_type_id` = ctcee.`id`
            LEFT JOIN `chart_type` ct_hsat_mob ON pc_hsat_mob.`chart_type_id` = ct_hsat_mob.`id`
            LEFT JOIN `chart_status` csh ON pch.`chart_status_id` = csh.`id`
            LEFT JOIN `chart_status` cshri ON pchri.`chart_status_id` = cshri.`id`
            LEFT JOIN `chart_status` cscs ON pccs.`chart_status_id` = cscs.`id`
            LEFT JOIN `chart_status` csce ON pcce.`chart_status_id` = csce.`id`
            LEFT JOIN `chart_status` cscee ON pccee.`chart_status_id` = cscee.`id`
            LEFT JOIN `chart_status` csceee ON pccceee.`chart_status_id` = csceee.`id`
            LEFT JOIN `chart_status` csceeee ON pccceeee.`chart_status_id` = csceeee.`id`
            LEFT JOIN `chart_status` cs_oximetry ON pc_oximetry.`chart_status_id` = cs_oximetry.`id`
            LEFT JOIN `chart_status` cs_hsat_mob ON pc_hsat_mob.`chart_status_id` = cs_hsat_mob.`id`
            $sqlWhere
            AND p.`is_duplicate` = 0
            AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
            AND p.`full_name` IS NOT NULL
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf.`description` IS NOT NULL
            AND p.`is_duplicate` = 0
            AND p.`full_name` IS NOT NULL
            AND p.`referral_date` IS NOT NULL
            AND p.`is_test_patient` = 0
            AND p.`referral_date` != '0000-00-00 00:00:00'
            ORDER BY pch.`chart_date`, pc_hsat_mob.`chart_date`, pchri.`chart_date`, pccs.`chart_date`, pcce.`chart_date`, pccee.`chart_date` 
        ";

        $sql2 = "SELECT 
                  COUNT(DISTINCT p.`id`) AS referralCount,
                  SUM(IF(pch.`chart_date` IS NULL, 1, 0)) AS hsatCount,
                  SUM(IF(pc_hsat_mob.`chart_date` IS NULL, 1, 0)) AS hsatMobileCount,
                  SUM(IF(pchri.`chart_date` IS NULL, 1, 0)) AS hsatResultInterCount,
                  SUM(IF(pccs.`chart_date` IS NULL, 1, 0)) AS cpapSetupCount,
                  SUM(IF(pccceee.`chart_date` IS NULL, 1, 0)) AS cpapSetupSoldCount,
                  SUM(IF(pcce.`chart_date` IS NULL, 1, 0)) AS cpapEvalCount,
                  SUM(IF(pccee.`chart_date` IS NULL, 1, 0)) AS cpapEndEvalCount,
                  SUM(IF(pb.`billing_date` IS NULL, 1, 0)) AS invoicesCount
                FROM `patient` p
                LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                      SELECT
                          phf1.`id`
                       FROM `patient_health_facility` phf1
                       WHERE phf1.`end_date` IS NULL
                       AND phf1.`patient_id` = p.`id`
                       ORDER BY phf1.`created_at` DESC
                       LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                LEFT JOIN `patient_chart` pch ON pch.`id` = (
                  SELECT 
                    pct1.`id` 
                  FROM `patient_chart` pct1
                  LEFT JOIN `chart_type` ctt1 ON pct1.`chart_type_id` = ctt1.`id`
                  LEFT JOIN `chart_status` cs1 ON cs1.`id` = pct1.`chart_status_id`
                  WHERE ctt1.`description` = 'HSAT'
                  AND pct1.`chart_status_id` IS NOT NULL
                  AND pct1.`patient_id` = p.`id`
                  AND pct1.`is_deleted` = 0
                  ORDER BY pct1.`chart_date` DESC, cs1.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pchprov ON pchprov.`id` = (
                  SELECT 
                    pctprov.`id` 
                  FROM `patient_chart` pctprov
                  LEFT JOIN `chart_type` cttprov ON pctprov.`chart_type_id` = cttprov.`id`
                  LEFT JOIN `chart_status` csprov ON csprov.`id` = pctprov.`chart_status_id`
                  WHERE cttprov.`description` = 'HSAT'
                  AND csprov.`description` = 'Completed'
                  AND pctprov.`chart_status_id` IS NOT NULL
                  AND pctprov.`patient_id` = p.`id`
                  AND pctprov.`is_deleted` = 0
                  ORDER BY pctprov.`chart_date` DESC, csprov.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `provider` prov ON p.`provider_id` = prov.`id`
                LEFT JOIN `provider` pch_prov ON pchprov.`provider_id` = pch_prov.`id`
                LEFT JOIN `patient_chart` pchri ON pchri.`id` = (
                  SELECT 
                    pct2.`id` 
                  FROM `patient_chart` pct2
                  LEFT JOIN `chart_type` ctt2 ON pct2.`chart_type_id` = ctt2.`id`
                  LEFT JOIN `chart_status` cs2 ON cs2.`id` = pct2.`chart_status_id`
                  WHERE ctt2.`description` = 'HSAT Results Interpretation'
                  AND pct2.`chart_status_id` IS NOT NULL
                  AND pct2.`patient_id` = p.`id`
                  AND pct2.`is_deleted` = 0
                  ORDER BY pct2.`chart_date` DESC, cs2.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pccs ON pccs.`id` = (
                  SELECT 
                    pct3.`id` 
                  FROM `patient_chart` pct3
                  LEFT JOIN `chart_type` ctt3 ON pct3.`chart_type_id` = ctt3.`id`
                  LEFT JOIN `chart_status` cs3 ON cs3.`id` = pct3.`chart_status_id`
                  WHERE ctt3.`description` = 'CPAP Setup'
                  AND pct3.`chart_status_id` IS NOT NULL
                  AND pct3.`patient_id` = p.`id`
                  AND pct3.`is_deleted` = 0
                  ORDER BY pct3.`chart_date` DESC, cs3.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pcce ON pcce.`id` = (
                  SELECT 
                    pct4.`id` 
                  FROM `patient_chart` pct4
                  LEFT JOIN `chart_type` ctt4 ON pct4.`chart_type_id` = ctt4.`id`
                  LEFT JOIN `chart_status` cs4 ON cs4.`id` = pct4.`chart_status_id`
                  WHERE ctt4.`description` = 'CPAP Eval' 
                  AND pct4.`patient_id` = p.`id`
                  AND pct4.`chart_status_id` IS NOT NULL
                  AND pct4.`is_deleted` = 0
                  ORDER BY pct4.`chart_date` DESC, cs4.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pccee ON pccee.`id` = (
                  SELECT 
                    pct5.`id` 
                  FROM `patient_chart` pct5
                  LEFT JOIN `chart_type` ctt5 ON pct5.`chart_type_id` = ctt5.`id`
                  LEFT JOIN `chart_status` cs5 ON cs5.`id` = pct5.`chart_status_id`
                  WHERE ctt5.`description` = 'CPAP End Eval' 
                  AND pct5.`patient_id` = p.`id`
                  AND pct5.`chart_status_id` IS NOT NULL
                  AND pct5.`is_deleted` = 0
                  ORDER BY pct5.`chart_date` DESC, cs5.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pccceee ON pccceee.`id` = (
                  SELECT 
                    pct6.`id`
                  FROM `patient_chart` pct6
                  LEFT JOIN `chart_type` ctt6 ON pct6.`chart_type_id` = ctt6.`id`
                  LEFT JOIN `chart_status` cs6 ON cs6.`id` = pct6.`chart_status_id`
                  WHERE ctt6.`description` = 'CPAP Setup Sold'
                  AND pct6.`patient_id` = p.`id`
                  AND pct6.`chart_status_id` IS NOT NULL
                  AND pct6.`is_deleted` = 0
                  ORDER BY pct6.`chart_date` DESC, cs6.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pc_hsat_mob ON pc_hsat_mob.`id` = (
                  SELECT 
                    pct9.`id` 
                  FROM `patient_chart` pct9
                  LEFT JOIN `chart_type` ctt9 ON pct9.`chart_type_id` = ctt9.`id`
                  LEFT JOIN `chart_status` cs9 ON cs9.`id` = pct9.`chart_status_id`
                  WHERE ctt9.`description` = 'HSAT (Mobile)' 
                  AND pct9.`patient_id` = p.`id`
                  AND pct9.`chart_status_id` IS NOT NULL
                  AND pct9.`is_deleted` = 0
                  ORDER BY pct9.`chart_date` DESC, cs9.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pccceeee ON pccceeee.`id` = (
                  SELECT 
                    pct8.`id`
                  FROM `patient_chart` pct8
                  LEFT JOIN `chart_type` ctt8 ON pct8.`chart_type_id` = ctt8.`id`
                  LEFT JOIN `chart_status` css8 ON pct8.`chart_status_id` = css8.`id`
                  WHERE pct8.`patient_id` = p.`id`
                  $sqlWhere2
                  AND pct8.`patient_id` = p.`id`
                  AND pct8.`chart_status_id` IS NOT NULL
                  AND (pct8.`is_deleted` = 0 OR pct8.`is_deleted` IS NULL)
                  ORDER BY pct8.`chart_date` DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_billing` pb ON pb.`id` = (
                  SELECT 
                    pb1.`id`
                  FROM `patient_billing` pb1
                  LEFT JOIN `patient` pat ON pb1.`patient_id` = pat.`id`
                  LEFT JOIN `health_facility` hf ON pb1.`health_facility_id` = hf.`id` 
                  LEFT JOIN `patient_insurance` pi ON pb1.`patient_insurance_id` = pi.`id`
                  LEFT JOIN `insurance` i ON pi.`insurance_id` = i.`id`
                  WHERE pb1.`patient_id` = p.`id`
                  AND pb1.`billing_status` != 'Void'
                  ORDER BY pb1.`billing_date` DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_specialist` ps ON ps.`id` = (
                  SELECT ps1.`id`
                  FROM `patient_specialist` ps1
                  WHERE ps1.`is_deleted` = 0
                  AND ps1.`patient_id` = p.`id`
                  AND ps1.`end_date` IS NULL
                  ORDER BY ps1.`start_date` DESC
                  LIMIT 1
                )
                LEFT JOIN `specialist` sp ON ps.`specialist_id` = sp.`id` 
                LEFT JOIN `health_facility` hfpb ON pb.`health_facility_id` = hfpb.`id` 
                LEFT JOIN `chart_type` cth ON pch.`chart_type_id` = cth.`id`
                LEFT JOIN `chart_type` cthri ON pchri.`chart_type_id` = cthri.`id`
                LEFT JOIN `chart_type` ctcs ON pccs.`chart_type_id` = ctcs.`id`
                LEFT JOIN `chart_type` ctce ON pcce.`chart_type_id` = ctce.`id`
                LEFT JOIN `chart_type` ctcee ON pccee.`chart_type_id` = ctcee.`id`
                LEFT JOIN `chart_type` ctcss ON pccceee.`chart_type_id` = ctcss.`id`
                LEFT JOIN `chart_type` ct_hsat_mob ON pc_hsat_mob.`chart_type_id` = ct_hsat_mob.`id`
                LEFT JOIN `chart_status` csh ON pch.`chart_status_id` = csh.`id`
                LEFT JOIN `chart_status` cshri ON pchri.`chart_status_id` = cshri.`id`
                LEFT JOIN `chart_status` cscs ON pccs.`chart_status_id` = cscs.`id`
                LEFT JOIN `chart_status` csce ON pcce.`chart_status_id` = csce.`id`
                LEFT JOIN `chart_status` cscee ON pccee.`chart_status_id` = cscee.`id`
                LEFT JOIN `chart_status` csceee ON pccceee.`chart_status_id` = csceee.`id`
                LEFT JOIN `chart_status` csceeee ON pccceeee.`chart_status_id` = csceeee.`id`
                LEFT JOIN `chart_status` cs_hsat_mob ON pc_hsat_mob.`chart_status_id` = cs_hsat_mob.`id`
                $sqlWhere
                AND p.`is_duplicate` = 0
                AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
                AND p.`full_name` IS NOT NULL
                AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND hf.`description` IS NOT NULL
                AND p.`referral_date` IS NOT NULL
                AND p.`referral_date` != '0000-00-00 00:00:00'
                AND p.`is_test_patient` = 0
                ORDER BY p.`referral_date`, pch.`chart_date`, pc_hsat_mob.`chart_date`, pchri.`chart_date`, pccs.`chart_date`, pcce.`chart_date`, pccee.`chart_date`";

        $sql3 = "SELECT 
                  phf.`health_facility_id`,
                  hf.`description` AS branchDesc,
                  COUNT(phf.`health_facility_id`) AS branchCount
                FROM `patient` p  
                LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                      SELECT
                          phf1.`id`
                       FROM `patient_health_facility` phf1
                       WHERE phf1.`end_date` IS NULL
                       AND phf1.`patient_id` = p.`id`
                       ORDER BY phf1.`created_at` DESC
                       LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                LEFT JOIN `patient_chart` pch ON pch.`id` = (
                  SELECT 
                    pct1.`id` 
                  FROM `patient_chart` pct1
                  LEFT JOIN `chart_type` ctt1 ON pct1.`chart_type_id` = ctt1.`id`
                  LEFT JOIN `chart_status` cs1 ON cs1.`id` = pct1.`chart_status_id`
                  WHERE ctt1.`description` = 'HSAT'
                  AND pct1.`chart_status_id` IS NOT NULL
                  AND pct1.`patient_id` = p.`id`
                  AND pct1.`is_deleted` = 0
                  ORDER BY pct1.`chart_date` DESC, cs1.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pchprov ON pchprov.`id` = (
                  SELECT 
                    pctprov.`id` 
                  FROM `patient_chart` pctprov
                  LEFT JOIN `chart_type` cttprov ON pctprov.`chart_type_id` = cttprov.`id`
                  LEFT JOIN `chart_status` csprov ON csprov.`id` = pctprov.`chart_status_id`
                  WHERE cttprov.`description` = 'HSAT'
                  AND csprov.`description` = 'Completed'
                  AND pctprov.`chart_status_id` IS NOT NULL
                  AND pctprov.`patient_id` = p.`id`
                  AND pctprov.`is_deleted` = 0
                  ORDER BY pctprov.`chart_date` DESC, csprov.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `provider` prov ON p.`provider_id` = prov.`id`
                LEFT JOIN `provider` pch_prov ON pchprov.`provider_id` = pch_prov.`id`
                LEFT JOIN `patient_chart` pchri ON pchri.`id` = (
                  SELECT 
                    pct2.`id` 
                  FROM `patient_chart` pct2
                  LEFT JOIN `chart_type` ctt2 ON pct2.`chart_type_id` = ctt2.`id`
                  LEFT JOIN `chart_status` cs2 ON cs2.`id` = pct2.`chart_status_id`
                  WHERE ctt2.`description` = 'HSAT Results Interpretation'
                  AND pct2.`chart_status_id` IS NOT NULL
                  AND pct2.`patient_id` = p.`id`
                  AND pct2.`is_deleted` = 0
                  ORDER BY pct2.`chart_date` DESC, cs2.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pccs ON pccs.`id` = (
                  SELECT 
                    pct3.`id` 
                  FROM `patient_chart` pct3
                  LEFT JOIN `chart_type` ctt3 ON pct3.`chart_type_id` = ctt3.`id`
                  LEFT JOIN `chart_status` cs3 ON cs3.`id` = pct3.`chart_status_id`
                  WHERE ctt3.`description` = 'CPAP Setup'
                  AND pct3.`chart_status_id` IS NOT NULL
                  AND pct3.`patient_id` = p.`id`
                  AND pct3.`is_deleted` = 0
                  ORDER BY pct3.`chart_date` DESC, cs3.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pcce ON pcce.`id` = (
                  SELECT 
                    pct4.`id` 
                  FROM `patient_chart` pct4
                  LEFT JOIN `chart_type` ctt4 ON pct4.`chart_type_id` = ctt4.`id`
                  LEFT JOIN `chart_status` cs4 ON cs4.`id` = pct4.`chart_status_id`
                  WHERE ctt4.`description` = 'CPAP Eval' 
                  AND pct4.`chart_status_id` IS NOT NULL
                  AND pct4.`patient_id` = p.`id`
                  AND pct4.`is_deleted` = 0
                  ORDER BY pct4.`chart_date` DESC, cs4.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pccee ON pccee.`id` = (
                  SELECT 
                    pct5.`id` 
                  FROM `patient_chart` pct5
                  LEFT JOIN `chart_type` ctt5 ON pct5.`chart_type_id` = ctt5.`id`
                  LEFT JOIN `chart_status` cs5 ON cs5.`id` = pct5.`chart_status_id`
                  WHERE ctt5.`description` = 'CPAP End Eval' 
                  AND pct5.`chart_status_id` IS NOT NULL
                  AND pct5.`patient_id` = p.`id`
                  AND pct5.`is_deleted` = 0
                  ORDER BY pct5.`chart_date` DESC, cs5.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pccceee ON pccceee.`id` = (
                  SELECT 
                    pct6.`id`
                  FROM `patient_chart` pct6
                  LEFT JOIN `chart_type` ctt6 ON pct6.`chart_type_id` = ctt6.`id`
                  LEFT JOIN `chart_status` cs6 ON cs6.`id` = pct6.`chart_status_id`
                  WHERE ctt6.`description` = 'CPAP Setup Sold'
                  AND pct6.`chart_status_id` IS NOT NULL
                  AND pct6.`patient_id` = p.`id`
                  AND pct6.`is_deleted` = 0
                  ORDER BY pct6.`chart_date` DESC, cs6.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pc_hsat_mob ON pc_hsat_mob.`id` = (
                  SELECT 
                    pct9.`id` 
                  FROM `patient_chart` pct9
                  LEFT JOIN `chart_type` ctt9 ON pct9.`chart_type_id` = ctt9.`id`
                  LEFT JOIN `chart_status` cs9 ON cs9.`id` = pct9.`chart_status_id`
                  WHERE ctt9.`description` = 'HSAT (Mobile)' 
                  AND pct9.`patient_id` = p.`id`
                  AND pct9.`chart_status_id` IS NOT NULL
                  AND pct9.`is_deleted` = 0
                  ORDER BY pct9.`chart_date` DESC, cs9.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pccceeee ON pccceeee.`id` = (
                  SELECT 
                    pct8.`id`
                  FROM `patient_chart` pct8
                  LEFT JOIN `chart_type` ctt8 ON pct8.`chart_type_id` = ctt8.`id`
                  LEFT JOIN `chart_status` css8 ON pct8.`chart_status_id` = css8.`id`
                  WHERE pct8.`patient_id` = p.`id`
                  $sqlWhere2
                  AND pct8.`patient_id` = p.`id`
                  AND pct8.`chart_status_id` IS NOT NULL
                  AND (pct8.`is_deleted` = 0 OR pct8.`is_deleted` IS NULL)
                  ORDER BY pct8.`chart_date` DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_billing` pb ON pb.`id` = (
                  SELECT 
                    pb1.`id`
                  FROM `patient_billing` pb1
                  LEFT JOIN `patient` pat ON pb1.`patient_id` = pat.`id`
                  LEFT JOIN `health_facility` hf ON pb1.`health_facility_id` = hf.`id` 
                  LEFT JOIN `patient_insurance` pi ON pb1.`patient_insurance_id` = pi.`id`
                  LEFT JOIN `insurance` i ON pi.`insurance_id` = i.`id`
                  WHERE pb1.`patient_id` = p.`id`
                  AND pb1.`billing_status` != 'Void'
                  ORDER BY pb1.`billing_date` DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_specialist` ps ON ps.`id` = (
                  SELECT ps1.`id`
                  FROM `patient_specialist` ps1
                  WHERE ps1.`is_deleted` = 0
                  AND ps1.`patient_id` = p.`id`
                  AND ps1.`end_date` IS NULL
                  ORDER BY ps1.`start_date` DESC
                  LIMIT 1
                )
                LEFT JOIN `specialist` sp ON ps.`specialist_id` = sp.`id` 
                LEFT JOIN `health_facility` hfpb ON pb.`health_facility_id` = hfpb.`id` 
                LEFT JOIN `chart_type` cth ON pch.`chart_type_id` = cth.`id`
                LEFT JOIN `chart_type` cthri ON pchri.`chart_type_id` = cthri.`id`
                LEFT JOIN `chart_type` ctcs ON pccs.`chart_type_id` = ctcs.`id`
                LEFT JOIN `chart_type` ctce ON pcce.`chart_type_id` = ctce.`id`
                LEFT JOIN `chart_type` ctcee ON pccee.`chart_type_id` = ctcee.`id`
                LEFT JOIN `chart_type` ctcss ON pccceee.`chart_type_id` = ctcss.`id`
                LEFT JOIN `chart_type` ct_hsat_mob ON pc_hsat_mob.`chart_type_id` = ct_hsat_mob.`id`
                LEFT JOIN `chart_status` csh ON pch.`chart_status_id` = csh.`id`
                LEFT JOIN `chart_status` cshri ON pchri.`chart_status_id` = cshri.`id`
                LEFT JOIN `chart_status` cscs ON pccs.`chart_status_id` = cscs.`id`
                LEFT JOIN `chart_status` csce ON pcce.`chart_status_id` = csce.`id`
                LEFT JOIN `chart_status` cscee ON pccee.`chart_status_id` = cscee.`id`
                LEFT JOIN `chart_status` csceee ON pccceee.`chart_status_id` = csceee.`id`
                LEFT JOIN `chart_status` csceeee ON pccceeee.`chart_status_id` = csceeee.`id`
                LEFT JOIN `chart_status` cs_hsat_mob ON pc_hsat_mob.`chart_status_id` = cs_hsat_mob.`id`
                $sqlWhere
                AND p.`is_duplicate` = 0
                AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
                AND p.`full_name` IS NOT NULL
                AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND hf.`description` IS NOT NULL
                AND p.`referral_date` IS NOT NULL
                AND p.`referral_date` != '0000-00-00 00:00:00'
                AND p.`is_test_patient` = 0
                GROUP BY phf.`health_facility_id`";


        $sql5 = "SELECT 
                  pch.`provider_id`,
                  pch_prov.`full_name` AS primaryProvider,
                  SUM(IF(pch_prov.`full_name` IS NOT NULL, 1, 0)) primaryProviderCount
                FROM `patient` p
                LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                      SELECT
                          phf1.`id`
                       FROM `patient_health_facility` phf1
                       WHERE phf1.`end_date` IS NULL
                       AND phf1.`patient_id` = p.`id`
                       ORDER BY phf1.`created_at` DESC
                       LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                LEFT JOIN `patient_chart` pch ON pch.`id` = (
                  SELECT 
                    pct1.`id` 
                  FROM `patient_chart` pct1
                  LEFT JOIN `chart_type` ctt1 ON pct1.`chart_type_id` = ctt1.`id`
                  LEFT JOIN `chart_status` cs1 ON pct1.`chart_status_id` = cs1.`id`
                  WHERE ctt1.`description` = 'HSAT'
                  AND pct1.`chart_status_id` IS NOT NULL
                  AND pct1.`patient_id` = p.`id`
                  AND pct1.`is_deleted` = 0
                  ORDER BY pct1.`chart_date` DESC, cs1.`ordinal` DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pchprov ON pchprov.`id` = (
                  SELECT 
                    pctprov.`id` 
                  FROM `patient_chart` pctprov
                  LEFT JOIN `chart_type` cttprov ON pctprov.`chart_type_id` = cttprov.`id`
                  LEFT JOIN `chart_status` csprov ON csprov.`id` = pctprov.`chart_status_id`
                  WHERE cttprov.`description` = 'HSAT'
                  AND csprov.`description` = 'Completed'
                  AND pctprov.`chart_status_id` IS NOT NULL
                  AND pctprov.`patient_id` = p.`id`
                  AND pctprov.`is_deleted` = 0
                  ORDER BY pctprov.`chart_date` DESC, csprov.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `provider` prov ON p.`provider_id` = prov.`id`
                LEFT JOIN `provider` pch_prov ON pchprov.`provider_id` = pch_prov.`id`
                LEFT JOIN `patient_chart` pchri ON pchri.`id` = (
                  SELECT 
                    pct2.`id` 
                  FROM `patient_chart` pct2
                  LEFT JOIN `chart_type` ctt2 ON pct2.`chart_type_id` = ctt2.`id`
                  LEFT JOIN `chart_status` cs2 ON cs2.`id` = pct2.`chart_status_id`
                  WHERE ctt2.`description` = 'HSAT Results Interpretation'
                  AND pct2.`chart_status_id` IS NOT NULL
                  AND pct2.`patient_id` = p.`id`
                  AND pct2.`is_deleted` = 0
                  ORDER BY pct2.`chart_date` DESC, cs2.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pccs ON pccs.`id` = (
                  SELECT 
                    pct3.`id` 
                  FROM `patient_chart` pct3
                  LEFT JOIN `chart_type` ctt3 ON pct3.`chart_type_id` = ctt3.`id`
                  LEFT JOIN `chart_status` cs3 ON cs3.`id` = pct3.`chart_status_id`
                  WHERE ctt3.`description` = 'CPAP Setup'
                  AND pct3.`chart_status_id` IS NOT NULL
                  AND pct3.`patient_id` = p.`id`
                  AND pct3.`is_deleted` = 0
                  ORDER BY pct3.`chart_date` DESC, cs3.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pcce ON pcce.`id` = (
                  SELECT 
                    pct4.`id` 
                  FROM `patient_chart` pct4
                  LEFT JOIN `chart_type` ctt4 ON pct4.`chart_type_id` = ctt4.`id`
                  LEFT JOIN `chart_status` cs4 ON cs4.`id` = pct4.`chart_status_id`
                  WHERE ctt4.`description` = 'CPAP Eval' 
                  AND pct4.`chart_status_id` IS NOT NULL
                  AND pct4.`patient_id` = p.`id`
                  AND pct4.`is_deleted` = 0
                  ORDER BY pct4.`chart_date` DESC, cs4.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pccee ON pccee.`id` = (
                  SELECT 
                    pct5.`id` 
                  FROM `patient_chart` pct5
                  LEFT JOIN `chart_type` ctt5 ON pct5.`chart_type_id` = ctt5.`id`
                  LEFT JOIN `chart_status` cs5 ON cs5.`id` = pct5.`chart_status_id`
                  WHERE ctt5.`description` = 'CPAP End Eval' 
                  AND pct5.`chart_status_id` IS NOT NULL
                  AND pct5.`patient_id` = p.`id`
                  AND pct5.`is_deleted` = 0
                  ORDER BY pct5.`chart_date` DESC, cs5.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pccceee ON pccceee.`id` = (
                  SELECT 
                    pct6.`id`
                  FROM `patient_chart` pct6
                  LEFT JOIN `chart_type` ctt6 ON pct6.`chart_type_id` = ctt6.`id`
                  LEFT JOIN `chart_status` cs6 ON cs6.`id` = pct6.`chart_status_id`
                  WHERE ctt6.`description` = 'CPAP Setup Sold'
                  AND pct6.`patient_id` = p.`id`
                  AND pct6.`is_deleted` = 0
                  ORDER BY pct6.`chart_date` DESC, cs6.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pc_hsat_mob ON pc_hsat_mob.`id` = (
                  SELECT 
                    pct9.`id` 
                  FROM `patient_chart` pct9
                  LEFT JOIN `chart_type` ctt9 ON pct9.`chart_type_id` = ctt9.`id`
                  LEFT JOIN `chart_status` cs9 ON cs9.`id` = pct9.`chart_status_id`
                  WHERE ctt9.`description` = 'HSAT (Mobile)' 
                  AND pct9.`patient_id` = p.`id`
                  AND pct9.`chart_status_id` IS NOT NULL
                  AND pct9.`is_deleted` = 0
                  ORDER BY pct9.`chart_date` DESC, cs9.ordinal DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_chart` pccceeee ON pccceeee.`id` = (
                  SELECT 
                    pct8.`id`
                  FROM `patient_chart` pct8
                  LEFT JOIN `chart_type` ctt8 ON pct8.`chart_type_id` = ctt8.`id`
                  LEFT JOIN `chart_status` css8 ON pct8.`chart_status_id` = css8.`id`
                  WHERE pct8.`patient_id` = p.`id`
                  $sqlWhere2
                  AND pct8.`patient_id` = p.`id`
                  AND pct8.`chart_status_id` IS NOT NULL
                  AND (pct8.`is_deleted` = 0 OR pct8.`is_deleted` IS NULL)
                  ORDER BY pct8.`chart_date` DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_billing` pb ON pb.`id` = (
                  SELECT 
                    pb1.`id`
                  FROM `patient_billing` pb1
                  LEFT JOIN `patient` pat ON pb1.`patient_id` = pat.`id`
                  LEFT JOIN `health_facility` hf ON pb1.`health_facility_id` = hf.`id` 
                  LEFT JOIN `patient_insurance` pi ON pb1.`patient_insurance_id` = pi.`id`
                  LEFT JOIN `insurance` i ON pi.`insurance_id` = i.`id`
                  WHERE pb1.`patient_id` = p.`id`
                  AND pb1.`billing_status` != 'Void'
                  ORDER BY pb1.`billing_date` DESC
                  LIMIT 1
                )
                LEFT JOIN `patient_specialist` ps ON ps.`id` = (
                  SELECT ps1.`id`
                  FROM `patient_specialist` ps1
                  WHERE ps1.`is_deleted` = 0
                  AND ps1.`patient_id` = p.`id`
                  AND ps1.`end_date` IS NULL
                  ORDER BY ps1.`start_date` DESC
                  LIMIT 1
                )
                LEFT JOIN `specialist` sp ON ps.`specialist_id` = sp.`id` 
                LEFT JOIN `health_facility` hfpb ON pb.`health_facility_id` = hfpb.`id` 
                LEFT JOIN `chart_type` cth ON pch.`chart_type_id` = cth.`id`
                LEFT JOIN `chart_type` cthri ON pchri.`chart_type_id` = cthri.`id`
                LEFT JOIN `chart_type` ctcs ON pccs.`chart_type_id` = ctcs.`id`
                LEFT JOIN `chart_type` ctce ON pcce.`chart_type_id` = ctce.`id`
                LEFT JOIN `chart_type` ctcee ON pccee.`chart_type_id` = ctcee.`id`
                LEFT JOIN `chart_type` ctcss ON pccceee.`chart_type_id` = ctcss.`id`
                LEFT JOIN `chart_type` ct_hsat_mob ON pc_hsat_mob.`chart_type_id` = ct_hsat_mob.`id`
                LEFT JOIN `chart_status` csh ON pch.`chart_status_id` = csh.`id`
                LEFT JOIN `chart_status` cshri ON pchri.`chart_status_id` = cshri.`id`
                LEFT JOIN `chart_status` cscs ON pccs.`chart_status_id` = cscs.`id`
                LEFT JOIN `chart_status` csce ON pcce.`chart_status_id` = csce.`id`
                LEFT JOIN `chart_status` cscee ON pccee.`chart_status_id` = cscee.`id`
                LEFT JOIN `chart_status` csceee ON pccceee.`chart_status_id` = csceee.`id`
                LEFT JOIN `chart_status` csceeee ON pccceeee.`chart_status_id` = csceeee.`id`
                LEFT JOIN `chart_status` cs_hsat_mob ON pc_hsat_mob.`chart_status_id` = cs_hsat_mob.`id`
                $sqlWhere
                AND p.`is_duplicate` = 0
                AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
                AND p.`full_name` IS NOT NULL
                AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND hf.`description` IS NOT NULL
                AND pchprov.`provider_id` IS NOT NULL
                AND p.`is_test_patient` = 0
                GROUP BY pch.`provider_id`";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $query2 = $this->getEntityManager()->getConnection()->prepare($sql2);
        $query2->execute($stmtParams);

        $query3 = $this->getEntityManager()->getConnection()->prepare($sql3);
        $query3->execute($stmtParams);

//        $query4 = $this->getEntityManager()->getConnection()->prepare($sql4);
//        $query4->execute($stmtParams);

        $query5 = $this->getEntityManager()->getConnection()->prepare($sql5);
        $query5->execute($stmtParams);

        $result['booked'] = $query->fetch();
        $result['unbooked'] = $query2->fetch();
        $result['branch'] = $query3->fetchAll();
//        $result['overdue'] = $query4->fetch();
        $result['clinician'] = $query5->fetchAll();
        return $result;
    }

    public function reportStatus($params)
    {
        $sqlWhere = "WHERE (p.`is_deleted` = 0 OR p.`is_deleted` IS NULL)";
        $stmtParams = array();

        if (!is_null($params['date_from']) && $params['date_from'] !== 'NULL' && $params['date_from'] !== 'all' && !empty($params['date_from']) && !is_null($params['date_to']) && $params['date_to'] !== 'null' && $params['date_to'] !== 'all' && !empty($params['date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "p.`referral_date` BETWEEN :date_from AND :date_to";
            $stmtParams['date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-', '/', $params['date_from'])));
            $stmtParams['date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['date_to'])));
        }

        $sqlSales = "
            SELECT
              hf.`description` AS healthFacility,
              COUNT(DISTINCT p.`id`) AS referralCount,
              COUNT(DISTINCT pccs.`id`) AS cpapSoldCount,
              COUNT(DISTINCT pccee.`id`) AS cpapEndEvalCount,
              COUNT(DISTINCT pb.`id`) AS invoiceCount
            FROM `patient` p
            LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                      SELECT
                          phf1.`id`
                       FROM `patient_health_facility` phf1
                       WHERE phf1.`end_date` IS NULL
                       AND phf1.`patient_id` = p.`id`
                       ORDER BY phf1.`created_at` DESC
                       LIMIT 1
            )
            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
            LEFT JOIN `patient_chart` pccs ON pccs.`id` = (
              SELECT pct3.`id`
              FROM `patient_chart` pct3
              LEFT JOIN `chart_type` ctt3 ON pct3.`chart_type_id` = ctt3.`id`
              LEFT JOIN `chart_status` css3 ON pct3.`chart_status_id` = css3.`id`
              WHERE ctt3.`description` = 'CPAP Setup Sold'
              AND pct3.`patient_id` = p.`id`
              AND (pct3.`is_deleted` = 0 OR pct3.`is_deleted` IS NULL)
              ORDER BY pct3.`chart_date` DESC, css3.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pccee ON pccee.`id` = (
              SELECT pct5.`id`
              FROM `patient_chart` pct5
              LEFT JOIN `chart_type` ctt5 ON pct5.`chart_type_id` = ctt5.`id`
              LEFT JOIN `chart_status` css5 ON pct5.`chart_status_id` = css5.`id`
              WHERE ctt5.`description` = 'CPAP End Eval'
              AND pct5.`patient_id` = p.`id`
              AND (pct5.`is_deleted` = 0 OR pct5.`is_deleted` IS NULL)
              ORDER BY pct5.`chart_date` DESC, css5.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_billing` pb ON pb.`id` = (
              SELECT 
                pb1.`id`
              FROM `patient_billing` pb1
              LEFT JOIN `patient` pat ON pb1.`patient_id` = pat.`id`
              WHERE pb1.`patient_id` = p.`id`
              AND pb1.`is_deleted` = 0
              AND pb1.`health_facility_id` IS NOT NULL
              AND pb1.`billing_status` != 'Void'
              GROUP BY pb1.`patient_id`
              ORDER BY pb1.`billing_date` DESC
              LIMIT 1
            )
            LEFT JOIN `chart_type` ctcs ON pccs.`chart_type_id` = ctcs.`id`
            LEFT JOIN `chart_type` ctcee ON pccee.`chart_type_id` = ctcee.`id`
            LEFT JOIN `chart_status` cscs ON pccs.`chart_status_id` = cscs.`id`
            LEFT JOIN `chart_status` cscee ON pccee.`chart_status_id` = cscee.`id`
            $sqlWhere
            AND p.`referral_date` IS NOT NULL
            AND p.`is_duplicate` = 0
            AND p.`full_name` IS NOT NULL
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf.`description` IS NOT NULL
            AND p.`is_test_patient` = 0
            GROUP BY phf.`health_facility_id`
            ORDER BY hf.`description` ASC
        ";

        $sqlReferral = "
            SELECT
              hf.`description` AS healthFacility,
              COUNT(DISTINCT p.`id`) AS referralCount,
              SUM(IF(cs1.`description` = 'Declined', 1, 0)) AS declinedCount,
              SUM(IF(cs2.`description` = 'No Show', 1, 0)) AS noShowCount,
              SUM(IF(cs3.`description` = 'Cancelled', 1, 0)) AS cancelledCount
            FROM `patient` p
            LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                      SELECT
                          phf1.`id`
                       FROM `patient_health_facility` phf1
                       WHERE phf1.`end_date` IS NULL
                       AND phf1.`patient_id` = p.`id`
                       ORDER BY phf1.`created_at` DESC
                       LIMIT 1
            )
            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
            LEFT JOIN `patient_chart` pce ON pce.`id` = (
              SELECT pct3.`id`
              FROM `patient_chart` pct3
              LEFT JOIN `chart_type` ct ON pct3.`chart_type_id` = ct.`id`
              LEFT JOIN `chart_status` cs ON pct3.`chart_status_id` = cs.`id`
              -- WHERE cs.`description` = 'Declined'
              WHERE pct3.`patient_id` = p.`id`
              AND (pct3.`is_deleted` = 0 OR pct3.`is_deleted` IS NULL)
              ORDER BY pct3.`chart_date` DESC, cs.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pccee ON pccee.`id` = (
              SELECT pct4.`id`
              FROM `patient_chart` pct4
              LEFT JOIN `chart_type` ctt ON pct4.`chart_type_id` = ctt.`id`
              LEFT JOIN `chart_status` css ON pct4.`chart_status_id` = css.`id`
              -- WHERE css.`description` = 'No Show'
              WHERE pct4.`patient_id` = p.`id`
              AND (pct4.`is_deleted` = 0 OR pct4.`is_deleted` IS NULL)
              ORDER BY pct4.`chart_date` DESC, css.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_chart` pccceee ON pccceee.`id` = (
              SELECT pct5.`id`
              FROM `patient_chart` pct5
              LEFT JOIN `chart_type` cttt ON pct5.`chart_type_id` = cttt.`id`
              LEFT JOIN `chart_status` csss ON pct5.`chart_status_id` = csss.`id`
              -- WHERE csss.`description` = 'Cancelled'
              WHERE pct5.`patient_id` = p.`id`
              AND (pct5.`is_deleted` = 0 OR pct5.`is_deleted` IS NULL)
              ORDER BY pct5.`chart_date` DESC, csss.`ordinal` DESC
              LIMIT 1
            )
            LEFT JOIN `chart_status` cs1 ON pce.`chart_status_id` = cs1.`id`
            LEFT JOIN `chart_status` cs2 ON pccee.`chart_status_id` = cs2.`id`
            LEFT JOIN `chart_status` cs3 ON pccceee.`chart_status_id` = cs3.`id`
            $sqlWhere
            AND p.`is_duplicate` = 0
            AND p.`full_name` IS NOT NULL
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            AND hf.`description` IS NOT NULL
            AND hf.`is_deleted` = 0
            AND p.`is_test_patient` = 0
            GROUP BY hf.`id`
            ORDER BY hf.`description` ASC
        ";

//        $sqlInvoices = "
//             SELECT
//              hf_pb.`description` AS healthFacility,
//              COUNT(DISTINCT pb.`invoice_no`) AS invoiceCount
//            FROM `patient` p
//            LEFT JOIN `patient_billing` pb ON pb.`id` = (
//              SELECT
//                pb1.`id`
//              FROM `patient_billing` pb1
//              LEFT JOIN `patient` pat ON pb1.`patient_id` = pat.`id`
//              WHERE pb1.`patient_id` = p.`id`
//              GROUP BY pb1.`patient_id`
//              ORDER BY pb1.`billing_date` DESC
//              LIMIT 1
//            )
//            LEFT JOIN `health_facility` hf_pb ON pb.`health_facility_id` = hf_pb.`id`
//            $sqlWhere
//            AND p.`is_duplicate` = 0
//            AND p.`full_name` IS NOT NULL
//            AND hf_pb.`description` NOT IN('DemoLocation', 'test')
//            AND hf_pb.`description` IS NOT NULL
//            AND hf_pb.`is_deleted` = 0
//            GROUP BY hf_pb.`id`
//            ORDER BY hf_pb.`description` ASC
//        ";

        $querySales = $this->getEntityManager()->getConnection()->prepare($sqlSales);
        $querySales->execute($stmtParams);

        $queryReferral = $this->getEntityManager()->getConnection()->prepare($sqlReferral);
        $queryReferral->execute($stmtParams);

//        $queryInvoices = $this->getEntityManager()->getConnection()->prepare($sqlInvoices);
//        $queryInvoices->execute($stmtParams);

        $result['sales'] = $querySales->fetchAll();
        $result['referral'] = $queryReferral->fetchAll();
//        $result['invoices'] = $queryInvoices->fetchAll();
        return $result;
    }

    //REPORT CONVERSION
    public function reportConversion($params) {
        $sqlWhere = "WHERE (p.`is_deleted` = 0 OR p.`is_deleted` IS NOT NULL) ";
        $stmtParams = array();

        if (!is_null($params['date_from']) && $params['date_from'] !== 'NULL' && $params['date_from'] !== 'all' && !empty($params['date_from']) && !is_null($params['date_to']) && $params['date_to'] !== 'null' && $params['date_to'] !== 'all' && !empty($params['date_to'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "p.`referral_date` BETWEEN :date_from AND :date_to";
            $stmtParams['date_from'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
            $stmtParams['date_to'] = date('Y-m-d 23:59:59', strtotime(str_replace('-', '/', $params['date_to'])));
        }

        if(isset($params['company']) && !empty($params['company']) && 'NULL' !== strtoupper($params['company'])){
            $companies = explode(',' , $params['company']);
            if(count($companies) > 0){
                $co_ors = '';
                foreach($companies as $key => $companyId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "hf.`company_id` = :company_id_$key";
                    $stmtParams["company_id_$key"] = $companyId;
                }
                $sqlWhere .= " AND ($co_ors) ";
            }
        }

        if(isset($params['branch']) && !empty($params['branch']) && 'NULL' !== strtoupper($params['branch'])){
            $branches = explode(',' , $params['branch']);
            if(count($branches) > 0){
                $hf_ors = '';
                foreach($branches as $key => $branchId){
                    $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "phf.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

        if(isset($params['patient']) && !empty($params['patient']) && 'NULL' !== strtoupper($params['patient'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pat.`id` = :patient";
            $stmtParams['patient'] = $params['patient'];
        }

        if($params['company'] == 'null' && $params['branch'] == 'null') {
            if (!is_null($params['userData']) && !empty($params['userData'])) {
                if ($params['userData']['type'] != 'Super Admin') {
                    if (count($params['userData']['company']['companyBranches']) > 0) {
                        $hf_ors = '';
                        foreach ($params['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) ";
                    }
                }
            }
        }

        switch ($params['type']) {
            case '0' :
                $sqlWhere .= " AND p.`id` NOT IN(
                                    SELECT pc.`patient_id`
                                    FROM `patient_chart` pc
                                    WHERE pc.`is_deleted` = 0
                                    AND pc.`patient_id` = p.`id`)";
                break;
            case '1' :
                $sqlWhere .= " AND p.`id` IN(
                                    SELECT pc.`patient_id`
                                    FROM `patient_chart` pc
                                    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.`is_deleted` = 0
                                    AND pc.`patient_id` = p.`id`
                                    AND ct.`description` = 'HSAT'
                                    AND pc.`patient_id` NOT IN(
                                        SELECT pc1.`patient_id`
                                        FROM `patient_chart` pc1
                                        LEFT JOIN `chart_type` ct1 ON pc1.`chart_type_id` = ct1.`id`
                                        LEFT JOIN `chart_status` cs1 ON pc1.`chart_status_id` = cs1.`id`
                                        WHERE pc1.`is_deleted` = 0
                                        AND pc1.`patient_id` = pc.`patient_id`
                                        AND ct1.`description` = 'HSAT Results Interpretation'
                                    )
                                )";
                break;
            case '2' :
                $sqlWhere .= " AND p.`id` IN(
                                    SELECT pc.`patient_id`
                                    FROM `patient_chart` pc
                                    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.`is_deleted` = 0
                                    AND pc.`patient_id` = p.`id`
                                    AND ct.`description` = 'HSAT Results Interpretation'
                                    AND pc.`patient_id` NOT IN(
                                        SELECT pc1.`patient_id`
                                        FROM `patient_chart` pc1
                                        LEFT JOIN `chart_type` ct1 ON pc1.`chart_type_id` = ct1.`id`
                                        LEFT JOIN `chart_status` cs1 ON pc1.`chart_status_id` = cs1.`id`
                                        WHERE pc1.`is_deleted` = 0
                                        AND pc1.`patient_id` = pc.`patient_id`
                                        AND ct1.`description` = 'CPAP Setup'
                                    )
                                )";
                break;
            case '3' :
                $sqlWhere .= " AND p.`id` IN(
                                    SELECT pc.`patient_id`
                                    FROM `patient_chart` pc
                                    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.`is_deleted` = 0
                                    AND pc.`patient_id` = p.`id`
                                    AND ct.`description` = 'CPAP Setup'
                                    AND pc.`patient_id` NOT IN(
                                        SELECT pb.`patient_id`
                                        FROM `patient_billing` pb
                                        WHERE pb.`is_deleted` = 0
                                        AND (pb.`billing_status` != 'Void' OR pb.`billing_status` = 'Pending Payment')
                                        AND pb.`patient_id` = pc.`patient_id`
                                    )
                                )";
                break;
        }

        $sql = "SELECT
                    p.`id` AS patientId,
                    DATE_FORMAT(p.`referral_date`, '%m/%d/%Y') AS referralDate,
                    DATE_FORMAT(p.`receive_date`, '%m/%d/%Y') AS receivedDate,
                    p.`full_name` AS patientName, 
                    co.`description` AS companyDesc,
                    hf.`description` AS branchDesc
                FROM `patient` p
                LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                    SELECT
                    phf1.`id`
                   FROM `patient_health_facility` phf1
                   WHERE phf1.`end_date` IS NULL
                   AND phf1.`patient_id` = p.`id`
                   ORDER BY phf1.`created_at` DESC
                   LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                $sqlWhere
                AND p.`is_test_patient` = 0
                ORDER BY p.`referral_date` DESC
                ";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $result = $query->fetchAll();
        return $result;
    }
}
