<?php

namespace RxConnexionBundle\Repository;

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

        $errors = array();

        $action = $patient_form['action'];

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

            /*    $patientExist = $this->getEntityManager()->getRepository('RxConnexionBundle:PatientEntity')
                    ->createQueryBuilder('ep')
                    ->where('ep.id != :id')
                    ->andWhere('ep.description = :description')
                    ->setParameters(array(
                        'id' => $patient_form['id'],
                        'description' => $patient_form['description']
                    ))
                    ->getQuery()->getResult();

                if($patientExist)
                    $errors[] = 'Description already exist.';*/

            /*if(empty($patient_form['description']))
                $errors[] = 'Description should not be blank.';*/
            if(empty($patient_form['referral_date'])){
                $errors[] = 'Referral Date is required';
            }
            if(empty($patient_form['city'])){
                $errors[] = 'Invalid city. Please choose value on autocomplete data.';
            }
            if(empty($patient_form['postalCode'])){
                $errors[] = 'Invalid postal code. Please choose value on autocomplete data.';
            }
            /*if(empty($patient_form['health_facility'])){
                $errors[] = 'Invalid branch. Please choose value on autocomplete data.';
            }*/
            $birthDateExploded = explode('/', $patient_form['birth_date']);
            if(!checkdate($birthDateExploded[0], $birthDateExploded[1], $birthDateExploded[2])){
                $errors[] = 'Invalid birth date format ' . '(' . $patient_form['birth_date'] . ').';
            }
        }

        return $errors;
    }

    public function ajax_list(array $get, $userData=null){

        $columns = array(
            array('p.`id`', "p.`id`"),
            array('p.`full_name`', 'p.`full_name`'),
            array('p.`personal_health_no`', 'p.`personal_health_no`', 'phnNo'),
            array('hf.`description`', 'hf.`description`', 'healthFacility_desc'),
            array('p.`address`', 'p.`address`'),
            array('CONCAT_WS(",", p.`phone_no`, p.`phone_no_two`, p.`phone_no_three`)', 'CONCAT_WS(",", p.`phone_no`, p.`phone_no_two`, p.`phone_no_three`)' , 'phoneNo'),
            array('p.`gender`', 'p.`gender`'),
            array('p.`birth_date`', "DATE_FORMAT(p.`birth_date`, '%m/%d/%Y')", 'birthDate'),
            array('p.`phone_no`', 'p.`phone_no`', 'phoneNo'),
            array('p.`phone_no_two`', 'p.`phone_no_two`', 'phoneNo2'),
            array('p.`phone_no_three`', 'p.`phone_no_three`', 'phoneNo3'),
            array('p.`mobile_no`', 'p.`mobile_no`', 'mobileNo'),
            array('p.`mobile_no_two`', 'p.`mobile_no_two`', 'mobileNo2'),
            array('p.`mobile_no_three`', 'p.`mobile_no_three`', 'mobileNo3'),
            array('prf.`id`', 'prf.`id`', 'hasRedFlag'),
        );
        $asColumns = array(
            'p.`phone_no`',
            'p.`phone_no_two`',
            'p.`phone_no_three`',
            'p.`mobile_no`',
            'p.`mobile_no_two`',
            'p.`mobile_no_three`',
            "DATE_FORMAT(p.`birth_date`, '%m/%d/%Y')"
        );

        $select = "SELECT";
        $from = "FROM `patient` p";
        $joins = "LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                    SELECT phf2.`id`
                    FROM `patient_health_facility` phf2
                    WHERE phf2.`patient_id` = p.`id`
                    AND phf2.`end_date` IS NULL
                    AND phf2.`is_deleted` = 0
                    ORDER BY phf2.`start_date` DESC
                    LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`";
        $joins .= "LEFT JOIN `patient_red_flag` prf ON prf.`id` = (
                    SELECT prf_TMP.id
                    FROM `patient_red_flag` prf_TMP
                    WHERE prf_TMP.patient_id = p.id
                    AND prf_TMP.end_date IS NULL
                    AND prf_TMP.is_deleted = 0
                    LIMIT 1
                )";
        $sqlWhere = "WHERE p.`is_deleted` = 0";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array();
        if($get['healthFacility_id'] != 'All'){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . " phf.`health_facility_id` = :healthFacility_id";
            $stmtParams['healthFacility_id'] = $get['healthFacility_id'];
        } else{ # if all
            if(!is_null($userData) && !empty($userData)){
                if($userData['type'] != 'Super Admin'){
//                    if(count($userData['company']['companyBranches']) > 0){
//                        $hf_ors = '';
//                        foreach($userData['company']['companyBranches'] as $key => $branchId){
//                            $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "phf.`health_facility_id` = :healthFacility_id_$key";
//                            $stmtParams["healthFacility_id_$key"] = $branchId;
//                        }
//                        $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . " ($hf_ors) ";
//                    } else {
//                        $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . " (1 != 1) ";
//                    }

                    $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . " 
                        EXISTS (
                            SELECT uc.`id`
                            FROM `account_user_company` uc
                            WHERE uc.`account_user_id` = :userId
                            AND uc.`company_id` = hf.`company_id`
                            AND uc.`start_date` <= :now
                            AND (
                                uc.`end_date` IS NULL OR uc.`end_date` >= :now
                            )
                            LIMIT 1
                        ) 
                    ";

                    $stmtParams['userId'] = $userData['id'];
                    $stmtParams['now'] = date('Y-m-d');
                }
            }
        }
        if($get['is_active'] != 'All'){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . " p.`is_active` = :is_active";
            $stmtParams['is_active'] = $get['is_active'];
        }

        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['advancedSearch']) && $get['advancedSearch'] == 1) {
		    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'] . "%";
			    }
		    }
	    } else {
		    $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . ' p.`full_name` LIKE :searchValue ';
		    $stmtParams['searchValue'] = "%" . $get['search']['value'] . "%";
	    }

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

        $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ').'p.`is_deleted` = 0';

//        $stmtParams['now'] = date('Y-m-d 00:00:00');

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

    public function patient_redflag_ajax_list(array $get){

        $columns = array(
            array('pat.`red_flag_start_date`', "pat.`red_flag_start_date`", 'startDate'),
            array('pat.`red_flag_end_date`', "pat.`red_flag_end_date`", 'startDate'),
            array('pat.`id`', "pat.`id`"),
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `patient` pat";
        $joins = "";
        $sqlWhere = "WHERE pat.`patient_id` = :patient_id";
        $sqlWhere .= " AND (pc.`is_deleted` = 0 OR pc.`is_deleted` IS NULL)";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array(
            'patient_id' => base64_decode($get['patient_id'])
        );

        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'] . "%";
            }
        }

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

    public function reportMarketingEncounter($params) {
        $sqlWhere = "WHERE  pc.`is_deleted` = 0";
        $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 ") . "pc.`chart_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['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 ') . "pc.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

        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['encounter_type']) && !empty($params['encounter_type']) && 'NULL' !== strtoupper($params['encounter_type'])){
            $encounter_types = explode(',' , $params['encounter_type']);
            if(count($encounter_types) > 0){
                $co_ors = '';
                foreach($encounter_types as $key => $encounter_typeId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "pc.`chart_type_id` = :encounter_type_$key";
                    $stmtParams["encounter_type_$key"] = $encounter_typeId;
                }
                $sqlWhere .= " AND ($co_ors) ";
            }
        }

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

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

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

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

        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 ') . "pc.`health_facility_id` = $branchId";
                        }
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors) ";
                    } else {
                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
                    }
                }
            }
        }

        $sql = '';

        if ($params['type'] == 'rhf') {

            $sql = "
                 SELECT
                    pc.`id` AS encounterId,
                    p.`full_name` AS marketingStaffName,
                    pat.`id` AS referringHealthFacilityId,
                    pat.`description` AS referringHealthFacilityDesc,
                    co.`description` AS companyDesc,
                    hf.`description` AS branch,
                    ct.`description` AS encounterType,
                    DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') AS encounterDate,
                    cs.`description` AS encounterStatus,
                    au.`full_name` AS accountUser,
                    tmpNote.`notes`
                FROM `referring_health_facility_chart` pc
                LEFT JOIN `marketing_staff` p ON p.`id` = pc.`marketing_staff_id`
                LEFT JOIN `referring_health_facility` pat ON pat.`id` = pc.`referring_health_facility_id`
                LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
                LEFT JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
                LEFT JOIN `health_facility` hf ON hf.`id` = pc.`health_facility_id`
                LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                LEFT JOIN `account_user` au ON au.`username` = pc.`updated_by`
                LEFT JOIN (
                  SELECT
                    pnote.`referring_health_facility_id`,
                    GROUP_CONCAT(CONCAT_WS('|', pnote.`id`, CONCAT('[ ' , DATE_FORMAT(pnote.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', pnote.`created_by`, ' ] : ', pnote.`note`)) SEPARATOR ',' ) AS notes
                  FROM `referring_health_facility_note` pnote
                  WHERE pnote.`is_deleted` = 0
                  GROUP BY pnote.`referring_health_facility_id`
                  ORDER BY pnote.`created_at` DESC
                ) tmpNote ON tmpNote.`referring_health_facility_id` = pc.`referring_health_facility_id`
                $sqlWhere
                AND pc.`referring_health_facility_id` IS NOT NULL
                ORDER BY pc.`updated_at` DESC 
          ";

        }

        if ($params['type'] == 'rp') {

            $sql = "
                 SELECT
                    pc.`id` AS encounterId,
                    p.`full_name` AS marketingStaffName,
                    pat.`id` AS referringProviderId,
                    pat.`full_name` AS referringProviderDesc,
                    co.`description` AS companyDesc,
                    hf.`description` AS branch,
                    ct.`description` AS encounterType,
                    DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') AS encounterDate,
                    cs.`description` AS encounterStatus,
                    au.`full_name` AS accountUser,
                    tmpNote.`notes`
                FROM `referring_provider_chart` pc
                LEFT JOIN `marketing_staff` p ON p.`id` = pc.`marketing_staff_id`
                LEFT JOIN `referring_provider` pat ON pat.`id` = pc.`referring_provider_id`
                LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
                LEFT JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
                LEFT JOIN `health_facility` hf ON hf.`id` = pc.`health_facility_id`
                LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                LEFT JOIN `account_user` au ON au.`username` = pc.`updated_by`
                LEFT JOIN (
                  SELECT
                    pnote.`referring_provider_id`,
                    GROUP_CONCAT(CONCAT_WS('|', pnote.`id`, CONCAT('[ ' , DATE_FORMAT(pnote.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', pnote.`created_by`, ' ] : ', pnote.`note`)) SEPARATOR ',' ) AS notes
                  FROM `referring_provider_note` pnote
                  WHERE pnote.`is_deleted` = 0
                  GROUP BY pnote.`referring_provider_id`
                  ORDER BY pnote.`created_at` DESC
                ) tmpNote ON tmpNote.`referring_provider_id` = pc.`referring_provider_id`
                $sqlWhere
                AND pc.`referring_provider_id` IS NOT NULL
                ORDER BY pc.`updated_at` DESC 
          ";

        }


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

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    public function autocomplete_suggestions($q, $userData = null) {

        $conn = $this->getEntityManager()->getConnection();
        $where = " 
            WHERE (
                CONCAT_WS(', ', p.`last_name`, p.`first_name`) LIKE :description OR
                CONCAT_WS(' ',p.`first_name`, p.`last_name`) LIKE :description OR
                p.`full_name` LIKE :description OR 
                p.`first_name` LIKE :description OR 
                p.`last_name` LIKE :description 
            )
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
        ";

        $stmtParams = array(
            'description' => "%$q%"
        );

        $hf_where = '';

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

        /*$stmt = $conn->prepare("
            
            CREATE TEMPORARY TABLE IF NOT EXISTS `patient_chart_temp`
            (
            `patient_id` BIGINT,
            `chartDate` DATETIME,
            INDEX pct_index_temp (`patient_id`)
            );
               INSERT INTO `patient_chart_temp`
               (`patient_id`, `chartDate`)
               SELECT
                -- CONCAT_WS('|', pc.`id`, cs.`description`, ct.`description`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), hf.`description`, p.`full_name`) AS patientChartDetails
                 pc.`patient_id`,
                 MAX(pc.`chart_date`)
               FROM `patient_chart` pc
               LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
               $where
               GROUP BY pc.`patient_id`
             ;
        ");*/
        //$stmt->execute($stmtParams);
        //$stmt->closeCursor();


        /*$stmt2 = $conn->prepare("
            CREATE TEMPORARY TABLE IF NOT EXISTS `patient_chart_temp_group`
            (
            `patient_id` BIGINT,
            `chartDate` DATETIME,
            `patientChartDetails` TEXT,
            INDEX `pct_index_temp` (`patient_id`, `chartDate`)
            );
             INSERT INTO `patient_chart_temp_group`
              (`patient_id`, `chartDate`, `patientChartDetails`)
              SELECT 
                  pc.`patient_id`, 
                  pc.`chart_date`, 
                  -- pct.`patientChartDetails`
                  CONCAT_WS('|', pc.`id`, cs.`description`, ct.`description`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), hf.`description`, pd.`full_name`)
              FROM `patient_chart` pc
                LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
                LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`
                LEFT JOIN `provider` pd ON pc.`provider_id` = pd.`id`
                $where
              GROUP BY pc.`patient_id`, pc.`chart_date`
            ;
          
        ");*/
        //$stmt2->execute($stmtParams);
        //$stmt2->closeCursor();

        $sql = $conn->prepare(
            "
            SELECT
                p.`id`,
                p.`address`,
                p.`geo_lat` AS geoLat,
                p.`geo_long` AS geoLong,
                DATE_FORMAT(p.`birth_date`, '%m/%d/%Y') AS birthDate,
                p.`personal_health_no`,
                CONCAT_WS(', ', p.`mobile_no`, p.`mobile_no_two`, p.`mobile_no_three`) AS mobileNos,
                CONCAT_WS(', ', p.`phone_no`, p.`phone_no_two`, p.`phone_no_three`) AS phoneNos,
                CONCAT_WS('|', pc.`id`, cs.`description`, ct.`description`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), hf.`description`, pd.`full_name`) AS patientChartDetails,
                p.`city_id`,
                pi.`insurances`,
                -- pc.`patientChartDetails`,
                CONCAT_WS(', ', p.`last_name`, p.`first_name`) AS data,
                CONCAT_WS(' - ', CONCAT_WS(', ', p.`last_name`, p.`first_name`),hf2.`description`) AS value,
                prp.`referringProviders`,
                prhf.`referringHealthFacilities`
            FROM `patient` p
            -- LEFT JOIN `patient_chart_temp` pct ON p.`id` = pct.`patient_id`
            LEFT JOIN (
                  SELECT
                    pi.`patient_id`,
                    GROUP_CONCAT(i.`description` SEPARATOR ', ') AS insurances
                  FROM `patient_insurance` pi
                  LEFT JOIN `insurance` i ON pi.`insurance_id` = i.`id`
                  WHERE pi.`is_deleted` = 0
                  GROUP BY pi.`patient_id` 
            ) pi ON pi.`patient_id` = p.`id`
            LEFT JOIN (
               SELECT
                pc.`id`,
                -- CONCAT_WS('|', pc.`id`, cs.`description`, ct.`description`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), hf.`description`, p.`full_name`) AS patientChartDetails
                 pc.`patient_id`,
                 MAX(pc.`chart_date`) AS chart_date
               FROM `patient_chart` pc
               LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
               $where
               GROUP BY pc.`patient_id`
            ) pct ON p.`id` = pct.`patient_id`
            -- LEFT JOIN `patient_chart_temp_group` pc ON pct.`patient_id` = pc.`patient_id` AND pct.`chartDate` = pc.`chartDate`
            LEFT JOIN(
              SELECT
                  prpt.`patient_id`, 
                  GROUP_CONCAT(CONCAT_WS(' ', rp.`first_name`, rp.`last_name`) SEPARATOR ', ') AS referringProviders
              FROM `patient_referring_provider` prpt
              LEFT JOIN `referring_provider` rp ON prpt.`referring_provider_id` = rp.`id`
              WHERE prpt.`start_date` <= :now
                AND (prpt.`end_date` IS NULL OR prpt.`end_date` >= :now)
              GROUP BY prpt.`patient_id`
            ) prp ON p.`id` = prp.`patient_id`
            LEFT JOIN(
              SELECT
                  prhft.`patient_id`, 
                  GROUP_CONCAT(hf.`description` SEPARATOR ', ') AS referringHealthFacilities
              FROM `patient_referring_health_facility` prhft
              LEFT JOIN `referring_health_facility` hf ON prhft.`referring_health_facility_id` = hf.`id`
              WHERE prhft.`start_date` <= :now
                AND (prhft.`end_date` IS NULL OR prhft.`end_date` >= :now)
              GROUP BY prhft.`patient_id`
            ) prhf ON p.`id` = prhf.`patient_id`
            LEFT JOIN (
                SELECT
                  pc.`id`,
                  pc.`patient_id`, 
                  pc.`chart_date`,
                  pc.`chart_status_id`,
                  pc.`chart_type_id`,
                  pc.`health_facility_id`,
                  pc.`provider_id`
                  -- pct.`patientChartDetails`
                  -- CONCAT_WS('|', pc.`id`, cs.`description`, ct.`description`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), hf.`description`, pd.`full_name`) AS patientChartDetails
                FROM `patient_chart` pc
                LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
                -- LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                -- LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                -- LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`
                -- LEFT JOIN `provider` pd ON pc.`provider_id` = pd.`id`
                $where
                GROUP BY pc.`patient_id`, pc.`chart_date`
            ) pc ON pct.`patient_id` = pc.`patient_id` AND pct.`chart_date` = pc.`chart_date`
            -- LEFT JOIN (
            --     SELECT * 
            --     FROM `patient_chart`
            --     GROUP BY `patient_id`, `chart_date`
            -- ) pc ON pct.`patient_id` = pc.`patient_id` AND pct.`chartDate` = pc.`chart_date`
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`
            -- LEFT JOIN `health_facility` hf2 ON hf2.`id` = IF(p.`health_facility_id` IS NOT NULL, p.`health_facility_id`, 
            --  (SELECT phf2.`health_facility_id`
            --  FROM `patient_health_facility` phf2
            --  WHERE phf2.`patient_id` = p.`id`
            --    AND phf2.`start_date` <= NOW()
            --    AND (phf2.`end_date` IS NULL OR phf2.`end_date` >= NOW())
            --  LIMIT 1) )
            LEFT JOIN `health_facility` hf2 ON hf2.`id` = (SELECT phf2.`health_facility_id`
              FROM `patient_health_facility` phf2
              WHERE phf2.`patient_id` = p.`id`
                AND phf2.`start_date` <= NOW()
                AND (phf2.`end_date` IS NULL OR phf2.`end_date` >= NOW())
              LIMIT 1 )
            LEFT JOIN `provider` pd ON pc.`provider_id` = pd.`id`
            -- LEFT JOIN (
            -- SELECT *
            -- FROM `patient_chart_temp` pct
            -- GROUP BY pct.`patient_id`
            -- ) pct ON p.`id` = pct.`patient_id`
            $where
            $hf_where
            ORDER BY data ASC
            LIMIT 0,40
        "
        );
        $stmtParams['now'] = date('Y-m-d 00:00:00');
        $sql->execute($stmtParams);

        return $sql->fetchAll();


    }

    public function autocomplete_for_billing_suggestions($q, $userData = null) {

        $conn = $this->getEntityManager()->getConnection();
        $where = " 
            WHERE (
                CONCAT_WS(', ', p.`last_name`, p.`first_name`) LIKE :description OR
                CONCAT_WS(' ',p.`first_name`, p.`last_name`) LIKE :description OR
                p.`full_name` LIKE :description OR 
                p.`first_name` LIKE :description OR 
                p.`last_name` LIKE :description 
            )
            AND p.`is_deleted` = 0
        ";
        $stmtParams = array(
            'description' => "%$q%"
        );

        $hf_where = '';
        if(!is_null($userData) && !empty($userData)){
            if($userData['type'] != 'Super Admin'){
                if(count($userData['branches']) > 0){
                    $hf_ors = '';
                    foreach($userData['branches'] as $key => $branchId){
                        $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "hf.`id` = :healthFacility_id_$key";
                        $stmtParams["healthFacility_id_$key"] = $branchId;
                    }
                    $hf_where .= " AND ($hf_ors) ";
                } else {
                    $hf_where .= " AND (1 != 1) ";
                }
            }
        }

        $sql = $conn->prepare(
            "
            SELECT
                p.`id`,
                p.`address`,
                CONCAT_WS(', ', p.`last_name`, p.`first_name`) AS data,
                CONCAT_WS(' - ', CONCAT_WS(', ', p.`last_name`, p.`first_name`),hf.`description`) AS value
            FROM `patient` p
            LEFT JOIN `health_facility` hf ON hf.`id` = IF(p.`health_facility_id` IS NOT NULL, p.`health_facility_id`, 
                 (SELECT phf2.`health_facility_id`
                  FROM `patient_health_facility` phf2
                  WHERE phf2.`patient_id` = p.`id`
                    AND phf2.`start_date` <= NOW()
                    AND (phf2.`end_date` >= NOW() OR phf2.`end_date` IS NULL)
                  LIMIT 1) )
            $where
            $hf_where
            ORDER BY data ASC
            LIMIT 0,40
        "
        );
        $sql->execute($stmtParams);
        $results = $sql->fetchAll();
        $this->encodedId($results);

        return $results;
    }

    public function autocomplete_for_account_user_reminder($q, $userData = null) {

        $conn = $this->getEntityManager()->getConnection();
        $where = " 
            WHERE (
                CONCAT_WS(', ', p.`last_name`, p.`first_name`) LIKE :description OR
                CONCAT_WS(' ',p.`first_name`, p.`last_name`) LIKE :description OR
                p.`full_name` LIKE :description OR 
                p.`first_name` LIKE :description OR 
                p.`last_name` LIKE :description 
            )
            AND p.`is_deleted` = 0
        ";
        $stmtParams = array(
            'description' => "%$q%"
        );

        $hf_where = '';
        if(!is_null($userData) && !empty($userData)){
            if($userData['type'] != 'Super Admin'){
                if(count($userData['branches']) > 0){
                    $hf_ors = '';
                    foreach($userData['branches'] as $key => $branchId){
                        $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "hf.`id` = :healthFacility_id_$key";
                        $stmtParams["healthFacility_id_$key"] = $branchId;
                    }
                    $hf_where .= " AND ($hf_ors) ";
                } else {
                    $hf_where .= " AND (1 != 1) ";
                }
            }
        }

        $sql = $conn->prepare(
            "
            SELECT
                p.`id`,
                p.`address`,
                CONCAT_WS(', ', p.`last_name`, p.`first_name`) AS data,
                CONCAT_WS(' - ', CONCAT_WS(', ', p.`last_name`, p.`first_name`),hf.`description`) AS value
            FROM `patient` p
            LEFT JOIN `health_facility` hf ON hf.`id` = IF(p.`health_facility_id` IS NOT NULL, p.`health_facility_id`, 
                 (SELECT phf2.`health_facility_id`
                  FROM `patient_health_facility` phf2
                  WHERE phf2.`patient_id` = p.`id`
                    AND phf2.`start_date` <= NOW()
                    AND (phf2.`end_date` >= NOW() OR phf2.`end_date` IS NULL)
                  LIMIT 1) )
            $where
            $hf_where
            ORDER BY data ASC
            LIMIT 0,40
        "
        );
        $sql->execute($stmtParams);
        $results = $sql->fetchAll();
        $this->encodedId($results);

        return $results;
    }

    private function encodedId(&$results){
        foreach ($results as &$result){
            $encodedId = base64_encode($result['id']);
            unset($result['id']);
            $result['id'] = $encodedId;
        }
    }

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

        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['insurance']) && !empty($params['insurance']) && 'NULL' !== strtoupper($params['insurance'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pns.`insurance_id` = :insurance";
            $stmtParams['insurance'] = is_numeric($params['insurance']) ? $params['insurance'] : base64_decode($params['insurance']);
        }

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

        if(isset($params['province']) && !empty($params['province']) && 'NULL' !== strtoupper($params['province'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "provi.`province_id` = :province";
            $stmtParams['province'] = is_numeric($params['province']) ? $params['province'] : base64_decode($params['province']);
        }

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

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

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

        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
                p.`id` AS patientId,
                p.`full_name` AS fullName,
                ct.`description` AS cityDesc,
                provi.`description` patientProvince,
                post.`description` postalCode,
                hf.`description` patientBranch,
                co.`description` AS companyDesc,
                i.`description` AS patientInsurance,
                p.`is_active`
            FROM `patient` p
            LEFT JOIN `account` a ON p.`account_id` = a.`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` = 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 `city` ct ON p.`city_id` = ct.`id`
            LEFT JOIN `province` prv ON ct.`province_id` = prv.`id`
            LEFT JOIN `postal_code` post ON post.`id` = p.`postal_code_id`
            LEFT JOIN (SELECT 
						  c.`id`,
						  c.`province_id`,
						  prov.`description`
					   FROM `city` c 
					   LEFT JOIN `province` prov ON prov.`id` = c.`province_id`
					   ) provi 
					   ON provi.`id` = p.`city_id`
			LEFT JOIN `patient_insurance` pns ON pns.`id` = (
					SELECT pns1.`id`
					FROM `patient_insurance` pns1 
					WHERE (pns1.`is_deleted` = 0 OR pns1.`is_deleted` IS NOT NULL)
					AND pns1.`patient_id` = p.`id`
					AND (pns1.`end_date` = '0000-00-00 00:00:00' OR pns1.`end_date` IS NULL)
					ORDER BY pns1.`start_date` DESC
					LIMIT 1
			)
			LEFT JOIN `insurance` i ON pns.`insurance_id` = i.`id`
			LEFT JOIN `patient_referring_health_facility` prhf ON prhf.`id` = (
			  SELECT prhf1.`id`
			  FROM `patient_referring_health_facility` prhf1
			  WHERE prhf1.`patient_id` = p.`id`
			  AND (prhf1.`end_date` = '0000-00-00 00:00:00' OR prhf1.`end_date` IS NULL)
			  ORDER BY prhf1.`start_date` DESC
			  LIMIT 1
			)
			LEFT JOIN `patient_referring_provider` prp ON prp.`id` = (
			  SELECT prp1.`id`
			  FROM `patient_referring_provider` prp1
			  WHERE prp1.`patient_id` = p.`id`
			  AND (prp1.`end_date` = '0000-00-00 00:00:00' OR prp1.`end_date` IS NULL)
			  ORDER BY prp1.`start_date` DESC
			  LIMIT 1
			)
		    $sqlWhere
		    AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
			GROUP BY p.`id`
	        ORDER BY p.`id` DESC
        ";

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

        $result = $query->fetchAll();

        return $result;

    }

    public function patientExists($lastName, $firstName, $birthDate) {
        $stmtParams = array(
            'firstName' => $firstName,
            'lastName' => $lastName,
            'birthDate' => $birthDate
        );

        $conn = $this->getEntityManager()->getConnection();
        $sql = $conn->prepare(
            "
            SELECT
                p.`id`
            FROM `patient` p
            WHERE UPPER(p.`first_name`) = UPPER(:firstName)
            AND UPPER(p.`last_name`) = UPPER(:lastName)
            AND p.`birth_date` = :birthDate
            LIMIT 1
        "
        );
        $sql->execute($stmtParams);
        return $sql->fetchAll();
    }

    public function getPatientDetailsForHl7() {
        $conn = $this->getEntityManager()->getConnection();
        $sql = $conn->prepare(
            "
            SELECT
              p.`id`,
              DATE_FORMAT(p.`birth_date`, '%Y%m%d%H%i%s') AS birthDate,
              p.`alberta_health_card_no` AS albertaHealthCardNo,
              p.`first_name` AS firstName, 
              p.`last_name` AS lastName, 
              p.`middle_name` AS middleName,
              p.`full_name` AS fullName,
              p.`gender`, 
              p.`street`,
              c.`description` AS city,
              pc.`description` AS postalCode,
              prp.`patientReferringProviderDetails`,
              pd.`diagnosesDetails`,
              pi.`insurancesDetails`
            FROM `patient` p
            LEFT  JOIN `city` c ON p.`city_id` = c.`id`
            LEFT  JOIN `postal_code` pc ON p.`postal_code_id` = pc.`id`
            LEFT JOIN(
              SELECT
                  prpt.`patient_id`, 
                  GROUP_CONCAT(CONCAT_WS('|',rp.`id`, rp.`last_name`, rp.`first_name`, rp.`middle_name`) SEPARATOR '~') AS patientReferringProviderDetails
              FROM `patient_referring_provider` prpt
              LEFT JOIN `referring_provider` rp ON prpt.`referring_provider_id` = rp.`id`
              GROUP BY prpt.`patient_id`
            ) prp ON p.`id` = prp.`patient_id`
            LEFT JOIN(
              SELECT
                  pdt.`patient_id`, 
                  GROUP_CONCAT(d.`description` SEPARATOR '~') AS diagnosesDetails
              FROM `patient_diagnosis` pdt
              LEFT JOIN `diagnosis` d ON pdt.`diagnosis_id` = d.`id`
              GROUP BY pdt.`patient_id`
            ) pd ON p.`id` = pd.`patient_id`
            LEFT JOIN(
              SELECT
                  pit.`patient_id`, 
                  GROUP_CONCAT(CONCAT_WS('|',i.`id`, DATE_FORMAT(pit.`start_date`, '%Y%m%d%H%i%s'), DATE_FORMAT(pit.`expire_date`, '%Y%m%d%H%i%s'), i.`description`) SEPARATOR '~') AS insurancesDetails
              FROM `patient_insurance` pit
              LEFT JOIN `insurance` i ON pit.`insurance_id` = i.`id`
              GROUP BY pit.`patient_id`
            ) pi ON p.`id` = pi.`patient_id`
            WHERE p.`is_deleted` = 0
            ORDER BY p.`id` ASC
        "
        );
        $sql->execute();
        return $sql->fetchAll();
    }

    //DASHBOARD
    public function dashboardPatientNoCharts($params) {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = '';
        $stmtParams = array();
        if(isset($params['branch']) && $params['branch'][0] != 'All'){
            $ors = "";
            foreach ($params['branch'] as $key => $branch) {
                $ors .= (empty($ors) ? '' : ' OR ').'p.`health_facility_id` = :hf_id'.$key;
                $stmtParams['hf_id'.$key] = $branch;
            }
            $andWhere .= " AND ($ors)";
        }

        if(isset($params['month']) && $params['month'] != '' && $params['month'] != 'All'){
            $month = $params['month'];
            $andWhere .= ' AND MONTH(p.`created_at`) = :month';
            $stmtParams['month'] = $month;
        }

        if(isset($params['year']) && $params['year'] != '' && $params['year'] != 'All'){
            $year = $params['year'];
            $andWhere .= ' AND YEAR(p.`created_at`) = :year';
            $stmtParams['year'] = $year;
        }

        $sql = $conn->prepare(
            "
            SELECT 
                hf.`description` AS healthFacility,
                p.`id` AS patientId,
                p.`full_name` AS patientName,
                pb.`invoice_no` AS invoiceNo,
                pn.`id` AS note,
                pf.`parsed_description` AS fileDesc,
                pt.`ticket_id` AS ticketNo,
                rp.`full_name` AS referringProvider,
                rhf.`description` AS referringHealthFacility
            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.`is_deleted` = 0 OR phf1.`is_deleted` IS NOT 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_billing` pb ON pb.`id` = (
              SELECT
                  pb1.`id`
              FROM `patient_billing` pb1
              WHERE (pb1.`is_deleted` = 0 OR pb1.`is_deleted` IS NOT NULL)
              AND pb1.`patient_id` = p.`id`
              ORDER BY pb1.`billing_date` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_note` pn ON pn.`id` = (
                SELECT pn1.`id`
                FROM `patient_note` pn1
                WHERE (pn1.`is_deleted` = 0 OR pn1.`is_deleted` IS NOT NULL) 
                AND pn1.`patient_id` = p.`id`
                ORDER BY pn1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `patient_file` pf ON pf.`id` = (
                SELECT pf1.`id`
                FROM `patient_file` pf1
                WHERE (pf1.`is_deleted` = 0 OR pf1.`is_deleted` IS NOT NULL)
                AND pf1.`patient_id` = p.`id`
                ORDER BY pf1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `patient_ticket` pt ON pt.`id` = (
                SELECT pt1.`id`
                FROM `patient_ticket` pt1
                WHERE pt1.`is_deleted` = 0
                AND pt1.`patient_id` = p.`id`
                ORDER BY pt1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `ticket` t ON pt.`ticket_id` = t.`id`
            LEFT JOIN `patient_referring_provider` prp ON prp.`id` = (
                SELECT prp1.`id`
                FROM `patient_referring_provider` prp1
                WHERE prp1.`is_deleted` = 0
                AND prp1.`patient_id` = p.`id`
                AND prp1.`end_date` IS NULL
                ORDER BY prp1.`start_date` DESC
                LIMIT 1
            )
            LEFT JOIN `referring_provider` rp ON prp.`referring_provider_id` = rp.`id`
            LEFT JOIN `patient_referring_health_facility` prhf ON prhf.`id` = (
                SELECT prhf1.`id`
                FROM `patient_referring_health_facility` prhf1
                WHERE prhf1.`is_deleted` = 0
                AND prhf1.`patient_id` = p.`id`
                AND prhf1.`end_date` IS NULL
                ORDER BY prhf1.`start_date` DESC
                LIMIT 1
            )
            LEFT JOIN `referring_health_facility` rhf ON prhf.`referring_health_facility_id` = rhf.`id`
            WHERE p.`id` NOT IN (
              SELECT pc.`patient_id`
              FROM `patient_chart` pc
              LEFT JOIN `patient` pat ON pc.`patient_id` = pat.`id`
              WHERE (pc.`is_deleted` IS NOT NULL OR pc.`is_deleted` = 0)
              AND pat.`is_active` = 1
              AND pat.`is_deleted` = 0
              GROUP BY pc.`patient_id`
            )
            AND p.`id` NOT IN (
               SELECT pt.`patient_id`
               FROM `patient_ticket` pt
               LEFT JOIN `patient` pat2 ON pt.`patient_id` = pat2.`id`
               WHERE pat2.`is_active` = 1
               AND pat2.`is_deleted` = 0
             )
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
            $andWhere
            ORDER BY p.`created_at` DESC
        "
        );

        $sql->execute($stmtParams);
        return $sql->fetchAll();
    }

    public function dashboardPatientNoChartsSummary($params) {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = '';
        $stmtParams = array();
        if(isset($params['branch']) && $params['branch'][0] != 'All'){
            $ors = "";
            foreach ($params['branch'] as $key => $branch) {
                $ors .= (empty($ors) ? '' : ' OR ').'p.`health_facility_id` = :hf_id'.$key;
                $stmtParams['hf_id'.$key] = $branch;
            }
            $andWhere .= " AND ($ors)";
        }

        if(isset($params['month']) && $params['month'] != '' && $params['month'] != 'All'){
            $month = $params['month'];
            $andWhere .= ' AND MONTH(p.`created_at`) = :month';
            $stmtParams['month'] = $month;
        }

        if(isset($params['year']) && $params['year'] != '' && $params['year'] != 'All'){
            $year = $params['year'];
            $andWhere .= ' AND YEAR(p.`created_at`) = :year';
            $stmtParams['year'] = $year;
        }

        $sql = $conn->prepare(
            "
            SELECT
                CONCAT_WS(' : ', EXTRACT(YEAR FROM p.`created_at`), CONVERT(COUNT(EXTRACT(YEAR FROM p.`created_at`)), INT)) summaryCount
            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.`is_deleted` = 0 OR phf1.`is_deleted` IS NOT 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_billing` pb ON pb.`id` = (
              SELECT
                  pb1.`id`
              FROM `patient_billing` pb1
              WHERE (pb1.`is_deleted` = 0 OR pb1.`is_deleted` IS NOT NULL)
              AND pb1.`patient_id` = p.`id`
              ORDER BY pb1.`billing_date` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_note` pn ON pn.`id` = (
                SELECT pn1.`id`
                FROM `patient_note` pn1
                WHERE (pn1.`is_deleted` = 0 OR pn1.`is_deleted` IS NOT NULL) 
                AND pn1.`patient_id` = p.`id`
                ORDER BY pn1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `patient_file` pf ON pf.`id` = (
                SELECT pf1.`id`
                FROM `patient_file` pf1
                WHERE (pf1.`is_deleted` = 0 OR pf1.`is_deleted` IS NOT NULL)
                AND pf1.`patient_id` = p.`id`
                ORDER BY pf1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `patient_ticket` pt ON pt.`id` = (
                SELECT pt1.`id`
                FROM `patient_ticket` pt1
                WHERE pt1.`is_deleted` = 0
                AND pt1.`patient_id` = p.`id`
                ORDER BY pt1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `ticket` t ON pt.`ticket_id` = t.`id`
            LEFT JOIN `patient_referring_provider` prp ON prp.`id` = (
                SELECT prp1.`id`
                FROM `patient_referring_provider` prp1
                WHERE prp1.`is_deleted` = 0
                AND prp1.`patient_id` = p.`id`
                AND prp1.`end_date` IS NULL
                ORDER BY prp1.`start_date` DESC
                LIMIT 1
            )
            LEFT JOIN `referring_provider` rp ON prp.`referring_provider_id` = rp.`id`
            LEFT JOIN `patient_referring_health_facility` prhf ON prhf.`id` = (
                SELECT prhf1.`id`
                FROM `patient_referring_health_facility` prhf1
                WHERE prhf1.`is_deleted` = 0
                AND prhf1.`patient_id` = p.`id`
                AND prhf1.`end_date` IS NULL
                ORDER BY prhf1.`start_date` DESC
                LIMIT 1
            )
            LEFT JOIN `referring_health_facility` rhf ON prhf.`referring_health_facility_id` = rhf.`id`
            WHERE p.`id` NOT IN (
              SELECT pc.`patient_id`
              FROM `patient_chart` pc
              LEFT JOIN `patient` pat ON pc.`patient_id` = pat.`id`
              WHERE (pc.`is_deleted` IS NOT NULL OR pc.`is_deleted` = 0)
              AND pat.`is_active` = 1
              AND pat.`is_deleted` = 0
              GROUP BY pc.`patient_id`
            )
            AND p.`id` NOT IN (
               SELECT pt.`patient_id`
               FROM `patient_ticket` pt
               LEFT JOIN `patient` pat2 ON pt.`patient_id` = pat2.`id`
               WHERE pat2.`is_active` = 1
               AND pat2.`is_deleted` = 0
             )
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
            AND EXTRACT(YEAR FROM p.`created_at`) >= '2014'
            $andWhere
            GROUP BY EXTRACT(YEAR FROM p.`created_at`)
        "
        );
        $sql->execute($stmtParams);
        return $sql->fetchAll();
    }

    public function dashboardPatientNoChartsCount() {
        $conn = $this->getEntityManager()->getConnection();

        $sql = $conn->prepare(
            "
            SELECT
                COUNT(p.`id`) AS data
            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.`is_deleted` = 0 OR phf1.`is_deleted` IS NOT 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_billing` pb ON pb.`id` = (
              SELECT
                  pb1.`id`
              FROM `patient_billing` pb1
              WHERE (pb1.`is_deleted` = 0 OR pb1.`is_deleted` IS NOT NULL)
              AND pb1.`patient_id` = p.`id`
              ORDER BY pb1.`billing_date` DESC
              LIMIT 1
            )
            LEFT JOIN `patient_note` pn ON pn.`id` = (
                SELECT pn1.`id`
                FROM `patient_note` pn1
                WHERE (pn1.`is_deleted` = 0 OR pn1.`is_deleted` IS NOT NULL) 
                AND pn1.`patient_id` = p.`id`
                ORDER BY pn1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `patient_file` pf ON pf.`id` = (
                SELECT pf1.`id`
                FROM `patient_file` pf1
                WHERE (pf1.`is_deleted` = 0 OR pf1.`is_deleted` IS NOT NULL)
                AND pf1.`patient_id` = p.`id`
                ORDER BY pf1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `patient_ticket` pt ON pt.`id` = (
                SELECT pt1.`id`
                FROM `patient_ticket` pt1
                WHERE pt1.`is_deleted` = 0
                AND pt1.`patient_id` = p.`id`
                ORDER BY pt1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `ticket` t ON pt.`ticket_id` = t.`id`
            LEFT JOIN `patient_referring_provider` prp ON prp.`id` = (
                SELECT prp1.`id`
                FROM `patient_referring_provider` prp1
                WHERE prp1.`is_deleted` = 0
                AND prp1.`patient_id` = p.`id`
                AND prp1.`end_date` IS NULL
                ORDER BY prp1.`start_date` DESC
                LIMIT 1
            )
            LEFT JOIN `referring_provider` rp ON prp.`referring_provider_id` = rp.`id`
            LEFT JOIN `patient_referring_health_facility` prhf ON prhf.`id` = (
                SELECT prhf1.`id`
                FROM `patient_referring_health_facility` prhf1
                WHERE prhf1.`is_deleted` = 0
                AND prhf1.`patient_id` = p.`id`
                AND prhf1.`end_date` IS NULL
                ORDER BY prhf1.`start_date` DESC
                LIMIT 1
            )
            LEFT JOIN `referring_health_facility` rhf ON prhf.`referring_health_facility_id` = rhf.`id`
            WHERE p.`id` NOT IN (
              SELECT pc.`patient_id`
              FROM `patient_chart` pc
              LEFT JOIN `patient` pat ON pc.`patient_id` = pat.`id`
              WHERE (pc.`is_deleted` IS NOT NULL OR pc.`is_deleted` = 0)
              AND pat.`is_active` = 1
              AND pat.`is_deleted` = 0
              GROUP BY pc.`patient_id`
            )
            AND p.`id` NOT IN (
               SELECT pt.`patient_id`
               FROM `patient_ticket` pt
               LEFT JOIN `patient` pat2 ON pt.`patient_id` = pat2.`id`
               WHERE pat2.`is_active` = 1
               AND pat2.`is_deleted` = 0
             )
            AND p.`is_deleted` = 0
            AND p.`is_active` = 1 
        "
        );


        $sql->execute();

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

    }

    public function dashboardAppointmentNotice($params) {

        $andWhere = '';
        $stmtParams = array();

        if(isset($params['branch']) && $params['branch'][0] != 'All'){
            $ors = "";
            foreach ($params['branch'] as $key => $branch) {
                $ors .= (empty($ors) ? '' : ' OR ').'hf.`id` = :hf_id'.$key;
                $stmtParams['hf_id'.$key] = $branch;
            }
            $andWhere .= " AND ($ors)";
        }

        if(isset($params['month']) && $params['month'] != '' && $params['month'] != 'All'){
            $month = $params['month'];
            $andWhere .= ' AND MONTH(s.`schedule_date`) = :month';
            $stmtParams['month'] = $month;
        }

        if(isset($params['year']) && $params['year'] != '' && $params['year'] != 'All'){
            $year = $params['year'];
            $andWhere .= ' AND YEAR(s.`schedule_date`) = :year';
            $stmtParams['year'] = $year;
        }

        /*if(isset($get['chartType']) && $get['chartType'] != 'All'){
            $andWhere .= " AND s.`chart_type_id` = :chartType_id";
            $stmtParams['chartType_id'] = $get['chartType'];
        }*/

        $conn = $this->getEntityManager()->getConnection();
        $sql = $conn->prepare("
            SELECT
                s.`id` AS schedId,
                DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y') AS schedDate,
                s.`start_time` AS sTime,
                s.`end_time` AS eTime,
                hf.`description` AS hfDesc,
                pr.`full_name` AS provName,
                ct.`description` AS ctDesc,
                p.`id` AS ptId,
                p.`full_name` AS ptName
            FROM `schedule` s
            LEFT JOIN `chart_type` ct ON s.`chart_type_id` = ct.`id`
            LEFT JOIN `schedule_status` ss ON s.`schedule_status_id` = ss.`id` 
            LEFT JOIN `patient` p ON s.`patient_id` = p.`id` 
            LEFT JOIN `health_facility` hf ON s.`health_facility_id` = hf.`id`
            LEFT JOIN `provider` pr ON s.`provider_id` = pr.`id`
            LEFT JOIN (
                SELECT
                    anf.`schedule_id`,
                    COUNT(DISTINCT ftb.`id`) AS ctr
                FROM `fax_tran_outbound` ftb
                LEFT JOIN `appointment_notice_fax` anf ON ftb.`appointment_notice_fax_id` = anf.`id`
                LEFT JOIN `schedule` s ON anf.`schedule_id` = s.`id`
                LEFT JOIN `patient` p ON s.`patient_id` = p.`id`
                LEFT JOIN `schedule_status` ss ON s.`schedule_status_id` = ss.`id`
                WHERE ftb.`source` = 'SN'
                AND ftb.`status` = 1
                AND s.`schedule_date` != '0000-00-00 00:00:00'
                AND (s.`is_deleted` IS NULL OR s.`is_deleted` = 0)
                AND p.`is_active` = 1
                AND ss.`description` IN(
                  'Booked',
                  'Booked per PT Request'
                )
                GROUP BY anf.`schedule_id`
            ) ftb ON s.`id` = ftb.`schedule_id`
            WHERE s.`schedule_date` != '0000-00-00 00:00:00'
            AND (s.`is_deleted` IS NULL OR s.`is_deleted` = 0)
            AND p.`is_active` = 1
            AND ss.`description` IN(
                  'Booked',
                  'Booked per PT Request'
            )
            AND (ftb.`schedule_id` IS NULL OR ftb.`ctr` <= 0)
            AND ct.`description` IN(
            'HSAT', 
            'HSAT (Mobile)',
            'HSAT Mobile (Repeat)',
            'HSAT Curbside',
            'HSAT Curbside (Repeat)',
            'HSAT Repeat (No Charge)',
            'HSAT Repeat (Paid)'
            )
            $andWhere
            ORDER BY s.`schedule_date` DESC, hf.`description`
        ");

        $sql->execute($stmtParams);

        return $sql->fetchAll();
    }

    public function dashboardAppointmentNoticeSummary($params) {

        $andWhere = '';
        $stmtParams = array();

        if(isset($params['branch']) && $params['branch'] != 'All'){
            $ors = "";
            foreach ($params['branch'] as $key => $branch) {
                $ors .= (empty($ors) ? '' : ' OR ').'hf.`id` = :hf_id'.$key;
                $stmtParams['hf_id'.$key] = $branch;
            }
            $andWhere .= " AND ($ors)";
        }

        if(isset($params['month']) && $params['month'] != '' && $params['month'] != 'All'){
            $month = $params['month'];
            $andWhere .= ' AND MONTH(s.`schedule_date`) = :month';
            $stmtParams['month'] = $month;
        }

        if(isset($params['year']) && $params['year'] != '' && $params['year'] != 'All'){
            $year = $params['year'];
            $andWhere .= ' AND YEAR(s.`schedule_date`) = :year';
            $stmtParams['year'] = $year;
        }

        /*if(isset($get['chartType']) && $get['chartType'] != 'All'){
            $andWhere .= " AND s.`chart_type_id` = :chartType_id";
            $stmtParams['chartType_id'] = $get['chartType'];
        }*/

        $conn = $this->getEntityManager()->getConnection();
        $sql = $conn->prepare("
            SELECT
                CONCAT_WS(' : ', EXTRACT(YEAR FROM s.`schedule_date`), CONVERT(COUNT(EXTRACT(YEAR FROM s.`schedule_date`)), INT)) summaryCount
            FROM `schedule` s
            LEFT JOIN `chart_type` ct ON s.`chart_type_id` = ct.`id`
            LEFT JOIN `schedule_status` ss ON s.`schedule_status_id` = ss.`id` 
            LEFT JOIN `patient` p ON s.`patient_id` = p.`id` 
            LEFT JOIN `health_facility` hf ON s.`health_facility_id` = hf.`id`
            LEFT JOIN `provider` pr ON s.`provider_id` = pr.`id`
            LEFT JOIN (
                SELECT
                    anf.`schedule_id`,
                    COUNT(DISTINCT ftb.`id`) AS ctr
                FROM `fax_tran_outbound` ftb
                LEFT JOIN `appointment_notice_fax` anf ON ftb.`appointment_notice_fax_id` = anf.`id`
                LEFT JOIN `schedule` s ON anf.`schedule_id` = s.`id`
                LEFT JOIN `patient` p ON s.`patient_id` = p.`id`
                LEFT JOIN `schedule_status` ss ON s.`schedule_status_id` = ss.`id`
                WHERE ftb.`source` = 'SN'
                AND ftb.`status` = 1
                AND s.`schedule_date` != '0000-00-00 00:00:00'
                AND (s.`is_deleted` IS NULL OR s.`is_deleted` = 0)
                AND p.`is_active` = 1
                AND ss.`description` = 'Booked'
                GROUP BY anf.`schedule_id`
            ) ftb ON s.`id` = ftb.`schedule_id`
            WHERE s.`schedule_date` != '0000-00-00 00:00:00'
            AND (s.`is_deleted` IS NULL OR s.`is_deleted` = 0)
            AND p.`is_active` = 1
            AND ss.`description` = 'Booked'
            AND (ftb.`schedule_id` IS NULL OR ftb.`ctr` <= 0)
            AND ct.`description` IN('HSAT', 'HSAT (Mobile)')
            $andWhere
            GROUP BY EXTRACT(YEAR FROM s.`schedule_date`)
        ");

        $sql->execute($stmtParams);
        return $sql->fetchAll();
    }

    public function referralBy($params) {
        $sqlWhere = "WHERE p.`is_deleted` = 0";
        $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($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 
                  p.`id` AS patientId,
                  DATE_FORMAT(p.`referral_date`, '%m/%d/%Y') AS referralDate,
                  p.`full_name` AS patientName,
                  p.`address` AS address,
                  DATE_FORMAT(p.`birth_date`, '%m/%d/%Y') AS birthDate,
                  p.`gender`,
                  p.`email`,
                  p.phone_no AS contactNo,
                  co.`description` AS companyDesc,
                  hf.`description` AS healthFacilityDesc,
                  (SELECT COUNT(*) 
                   FROM `schedule` s1
                   WHERE s1.`patient_id` = rbp.`patient_id`
                   AND s1.`is_deleted` = 0
                  ) AS appointmentCnt,
                  DATE_FORMAT((SELECT s2.`schedule_date`
                   FROM `schedule` s2
                   WHERE s2.`patient_id` = rbp.`patient_id`
                   AND s2.`is_deleted` = 0
                   ORDER BY s2.`schedule_date` DESC
                   LIMIT 1
                  ), '%m/%d/%Y') AS lastAppt,
                  rbp.`referred_by` AS referredById, 
                  rb.`full_name` AS referredBy 
                FROM `referred_by_patient` rbp
                LEFT JOIN `patient` p ON rbp.`patient_id` = p.`id`
                LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                    SELECT phf2.`id`
                    FROM `patient_health_facility` phf2
                    WHERE phf2.`patient_id` = rbp.`patient_id`
                    AND phf2.`start_date` IS NOT NULL
                    AND phf2.`end_date` IS NULL
                    ORDER BY phf2.`start_date` 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` rb ON rbp.`referred_by` = rb.`id`
                $sqlWhere
                AND (p.`referral_date` != '0000-00-00 00:00:00' OR p.`referral_date` IS NOT NULL)";

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

        $result = $query->fetchAll();

        return $result;
    }

    public function reportPatientCycle($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 (!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(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($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) ";
//                    }
//                }
//            }
//        }


        $subquery_ack = "FROM `patient_letter` ack_pl
            LEFT JOIN `patient_letter_status` ack_pls ON ack_pls.`id` = ack_pl.`patient_letter_status_id`
            WHERE ack_pl.`patient_id` = p.`id`
            AND ack_pl.`is_deleted` = 0
            AND ack_pl.`letter_type` IN (
                'Acknowledgement of Referral',
                'FAX for Referral Acknoledgement (FRSC)',
                'Fax Acknowledgement for PFT & HSAT Referral (FRSC)',
                'Acknowledgement of Referral (PSS)',
                'Fax Acknowledgement for PFT Only (FRSC)',
                'Acknowledgement'
            )
            ORDER BY ack_pl.`created_at` DESC
            LIMIT 1
        ";

        $subquery_rdn = "FROM `patient_letter` rdn_pl
            LEFT JOIN `patient_letter_status` rdn_pls ON rdn_pls.`id` = rdn_pl.`patient_letter_status_id`
            WHERE rdn_pl.`patient_id` = p.`id`
            AND rdn_pl.`is_deleted` = 0
            AND rdn_pl.`letter_type` IN (
                'HSAT - No Book (FRSC)',
                'HSAT Unable to Contact (FRSC)',
                'HSAT No Rebook (FRSC)',
                'HSAT Refused to Repeat HSAT (FRSC)',
                'HST Refuse - Wants to talk to their Doctor first (FRSC)',
                'HST Refuse - Had HSAT already (FRSC)',
                'HST Refuse - No Insurance-No Coverage (FRSC)',
                'Refusal of Care under Medical Advise (FRSC)',
                'No Booking Under 18 years of age (FRSC)',
                'HSAT No Book',
                'HSAT Unable to Contact',
                'HSAT No Rebook',
                'HSAT Refused to Repeat HSAT',
                'HSAT Refuse - Wants to talk to their Doctor first',
                'HSAT Refuse - Had HSAT already',
                'HSAT Refuse - No Insurance-No Coverage',
                'No Booking Under 18 years of age',
                'Unable to Proceed HSAT Due to High BMI'
            )
            ORDER BY rdn_pl.`created_at` DESC
            LIMIT 1
        ";

        $subquery_rl = " 
            FROM `patient_letter` rl_pl
            LEFT JOIN `patient_letter_status` rl_pls ON rl_pls.`id` = rl_pl.`patient_letter_status_id`
            WHERE rl_pl.`patient_id` = p.`id`
            AND rl_pl.`is_deleted` = 0
            AND rl_pl.`letter_type` IN (
                'Negative OSA (FRSC)',
                'Negative OSA - No RDI (FRSC)',
                'Oximetry Inconclusive (FRSC)',
                'Oximetry Negative (FRSC)',
                'Negative OSA',
                'Negative OSA - No RDI',
                'Oximetry Inconclusive',
                'Oximetry Negative',
                'Negative OSA (No RDI) (AHI)',
                'Oximetry Positive',
                'Oximetry Refuse',
                'Oximetry No Book'
            )
            ORDER BY rl_pl.`created_at` DESC
            LIMIT 1
        ";

        $subquery_cpap_pl = " 
            FROM `patient_letter` cpap_pl
            LEFT JOIN `patient_letter_status` cpap_pls ON cpap_pls.`id` = cpap_pl.`patient_letter_status_id`
            WHERE cpap_pl.`patient_id` = p.`id`
            AND cpap_pl.`is_deleted` = 0
            AND cpap_pl.`letter_type` IN (
                'Refusal of Care under Medical Advise',
                'CPAP Refuse - Medical Issues',
                'CPAP Refuse - No Funds-No Insurance-Note About Discussing Financing Options',
                'CPAP Refuse - Wants alternative therapy -consult ENT',
                'CPAP Refuse - Wants alternative therapy -Oral Appliance',
                'CPAP Refuse - Want to talk to Dr. prior to starting therapy (Very Mild to Moderate)',
                'CPAP Refuse - Want to talk to Dr. prior to starting therapy (Severe & Very Severe)',
                'CPAP Refuse - Wants to think about it (Severe & Very Severe)',
                'CPAP Refuse - Wants to think about it (Very Mild to Moderate)',
                'CPAP -- refused not interested (Severe to Very Severe)',
                'CPAP -- refused not interested (Very Mild to Moderate)',
                'CPAP No Book',
                'CPAP No Rebook',
                'Return CPAP -- Buy Online',
                'Return CPAP -- No Funds',
                'Return CPAP -- No Insurance',
                'Return CPAP -- Unable to Tolerate',
                'Return CPAP -- (No Stats) Buy online',
                'Return CPAP -- (No Stats) No insurance',
                'Return CPAP -- (No Stats) No Funds',
                'Return CPAP -- (No Stats) Try alternative treatment',
                'Return CPAP -- (No Stats) Unable to tolerate (Severe to Very Severe)',
                'Return CPAP -- (No Stats) Unable to tolerate (Very Mild to Moderate)',
                'CPAP Pressure Change'
              )
            ORDER BY cpap_pl.`created_at` DESC
            LIMIT 1
        ";

        $subquery_l_pl = " 
            FROM `patient_letter` l_pl
            LEFT JOIN `patient_letter_status` l_pls ON l_pls.`id` = l_pl.`patient_letter_status_id`
            WHERE l_pl.`patient_id` = p.`id`
            AND l_pl.`is_deleted` = 0
            AND l_pl.`letter_type` IN (
                'CPAP Pressure Change',
                'Adherence'
            )
            ORDER BY l_pl.`created_at` DESC
            LIMIT 1
        ";

        $subquery_pl_as = " 
            FROM `patient_letter` pl_as
            LEFT JOIN `account_user` au ON au.`id` = pl_as.`account_user_id`
            LEFT JOIN `patient_letter_status` l_pls ON l_pls.`id` = pl_as.`patient_letter_status_id`
            LEFT JOIN `patient_letter_note` pln ON pln.`patient_letter_id` = pl_as.`id`
            WHERE pl_as.`is_deleted` = 0
            ORDER BY pl_as.`created_at` DESC
            LIMIT 1
        ";

        $sql = "
            SELECT 
                p.`id`,
                DATE_FORMAT(p.`referral_date`, '%m/%d/%Y') AS referralDate,
                DATE_FORMAT(p.`receive_date`, '%m/%d/%Y') AS receiveDate,
                ftrn.`fax_category`,
                ftrn.`remote_id` AS receivedFax,
                p.`full_name`,
                rhf.`description` AS referringHealthFacility,
                rp.`full_name` AS referringProvider,
                temp_ps.`patientServices`,
                hf.`description` AS healthFacility,
                prev_hf.`description` AS prevHealthFacility,
                (
                    SELECT ack_pl.`id`
                    $subquery_ack
                ) AS ackId,
                (
                    SELECT ack_pl.`letter_type`
                    $subquery_ack
                ) AS ackLetterType,
                (
                    SELECT DATE_FORMAT(ack_pl.`updated_at`, '%m/%d/%Y')
                    $subquery_ack
                ) AS ackDate,
                (
                    SELECT ack_pls.`description`
                    $subquery_ack
                ) AS ackStatus,
                'ack_count_of_days',
                DATE_FORMAT(pc_ipc.`chart_date`, '%m/%d/%Y') AS ipcChartDate,
                cs_ipc.`description` AS ipcChartStatus,
                pc_ipc.`note` AS ipcNote,
                'ipc_count_of_days',
                DATE_FORMAT(pc_hsat.`chart_date`, '%m/%d/%Y') AS hsatChartDate,
                cs_hsat.`description` AS hsatChartStatus,
                pc_hsat.`note` AS hsatNote,
                DATE_FORMAT(fto.`created_at`, '%m/%d/%Y') AS appointmentNoticeFaxDate,
                'hsat_count_of_days',
                (
                    SELECT rdn_pl.`id`
                    $subquery_rdn
                ) AS rdnId,
                (
                    SELECT rdn_pl.`letter_type`
                    $subquery_rdn
                ) AS rdnLetterType,
                (
                    SELECT DATE_FORMAT(rdn_pl.`updated_at`, '%m/%d/%Y')
                    $subquery_rdn
                ) AS rdnDate,
                (
                    SELECT rdn_pls.`description`
                    $subquery_rdn
                ) AS rdnStatus,
                DATE_FORMAT(pc_hrd.`chart_date`, '%m/%d/%Y') AS hrdChartDate,
                cs_hrd.`description` AS hrdChartStatus,
                pc_hrd.`note` AS hrdNote,
                'hrd_count_of_days',
                'test_results',
                DATE_FORMAT(pc_hri.`chart_date`, '%m/%d/%Y') AS hriChartDate,
                cs_hri.`description` AS hriChartStatus,
                pc_hri.`note` AS hriNote,
                'hri_count_of_days',
                DATE_FORMAT(pc_pre.`chart_date`, '%m/%d/%Y') AS preChartDate,
                cs_pre.`description` AS preChartStatus,
                pc_pre.`note` AS preNote,
                (
                    SELECT rl_pl.`id`
                    $subquery_rl
                ) AS rlId,
                (
                    SELECT rl_pl.`letter_type`
                    $subquery_rl
                ) AS rlLetterType,
                (
                    SELECT DATE_FORMAT(rl_pl.`updated_at`, '%m/%d/%Y')
                    $subquery_rl
                ) AS rlDate,
                (
                    SELECT rl_pls.`description`
                    $subquery_rl
                ) AS rlStatus,
                'letter_assign_date',
                'letter_assign_status',
                'letter_assign_note',
                DATE_FORMAT(pc_quo.`chart_date`, '%m/%d/%Y') AS quoChartDate,
                cs_quo.`description` AS quoChartStatus,
                pc_quo.`note` AS quoNote,
                'quo_count_of_days',
                i.`description` AS insuranceDesc,
                DATE_FORMAT(pc_cpap_t.`chart_date`, '%m/%d/%Y') AS cpap_tChartDate,
                cs_cpap_t.`description` AS cpap_tChartStatus,
                pc_cpap_t.`note` AS cpap_tNote,
                DATE_FORMAT(pc_cpap_ss.`chart_date`, '%m/%d/%Y') AS cpap_ssChartDate,
                cs_cpap_ss.`description` AS cpap_ssChartStatus,
                pc_cpap_ss.`note` AS cpap_ssNote,
                (
                    SELECT cpap_pl.`id`
                    $subquery_cpap_pl
                ) AS cpap_plId,
                (
                    SELECT cpap_pl.`letter_type`
                    $subquery_cpap_pl
                ) AS cpap_plLetterType,
                (
                    SELECT DATE_FORMAT(cpap_pl.`updated_at`, '%m/%d/%Y')
                    $subquery_cpap_pl
                ) AS cpap_plDate,
                (
                    SELECT cpap_pls.`description`
                    $subquery_cpap_pl
                ) AS cpap_plStatus,
                DATE_FORMAT(pb.`billing_date`, '%m/%d/%Y') AS billingDate,
                pb.`invoice_no`,
                pb.`billing_status`,
                'pb_count_of_days',
                DATE_FORMAT(pc_lcc.`chart_date`, '%m/%d/%Y') AS pc_lccChartDate,
                cs_lcc.`description` AS cs_lccChartStatus,
                pc_lcc.`note` AS cs_lccNote,
                DATE_FORMAT(pc_six_ff.`chart_date`, '%m/%d/%Y') AS six_ffChartDate,
                cs_six_ff.`description` AS six_ffChartStatus,
                pc_six_ff.`note` AS cs_six_ffNote,
                'six_ff_count_of_days',
                DATE_FORMAT(pc_six_ff_latest.`chart_date`, '%m/%d/%Y') AS latestSixFFDate,
                DATE_FORMAT(DATE_ADD(pc_six_ff_latest.`chart_date`, INTERVAL 6 MONTH), '%m/%d/%Y') AS sixFFDateSixMonth,
                (
                    SELECT l_pl.`id`
                    $subquery_l_pl
                ) AS l_plId,
                (
                    SELECT l_pl.`letter_type`
                    $subquery_l_pl
                ) AS l_plLetterType,
                (
                    SELECT DATE_FORMAT(l_pl.`updated_at`, '%m/%d/%Y')
                    $subquery_l_pl
                ) AS l_plDate,
                (
                    SELECT l_pls.`description`
                    $subquery_l_pl
                ) AS l_plStatus,
                DATE_FORMAT(pb_latest.`billing_date`, '%m/%d/%Y') AS latestBillingDate,
                pb_latest.`invoice_no` AS latestInvoice,
                pb_latest.`billing_status` AS latestBillingStatus,
                pf.`id` ptFileId,
                ftrn.`id` AS faxId,
                ft.`description` AS fileType,
                pf.`description` AS fileName,
                (
                    SELECT CONCAT(au.`full_name`,' (',DATE_FORMAT(pl_as.`updated_at`, '%m/%d/%Y'),')')
                    $subquery_pl_as
                ) AS pl_asDate,
                (
                    SELECT l_pls.`description`
                    $subquery_pl_as
                ) AS pl_asStatus
            FROM `patient` p
            LEFT JOIN `patient_referring_health_facility` prhf ON prhf.`id` = (
                SELECT prhf1.`id`
                FROM `patient_referring_health_facility` prhf1
                WHERE prhf1.`is_deleted` = 0
                AND p.`id` = prhf1.`patient_id`
                AND prhf1.`start_date` <= :now
                AND (prhf1.`end_date` IS NULL OR prhf1.`end_date` > :now)
                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.`is_deleted` = 0
                AND p.`id` = prp1.`patient_id`
                AND prp1.`start_date` <= :now
                AND (prp1.`end_date` IS NULL OR prp1.`end_date` > :now)
                LIMIT 1
            )
            LEFT JOIN `referring_provider` rp ON rp.`id` = prp.`referring_provider_id`
            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`
                AND phf1.`start_date` <= :now
                AND (phf1.`end_date` IS NULL OR phf1.`end_date` >= :now)
                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
                    phf1.`id`
                FROM `patient_health_facility` phf1
                WHERE phf1.`end_date` IS NULL
                AND phf1.`patient_id` = p.`id`
                AND phf1.`start_date` <= :now
                AND (phf1.`end_date` IS NULL OR phf1.`end_date` >= :now)
                ORDER BY phf1.`created_at` ASC
                LIMIT 1
            )
            LEFT JOIN `health_facility` prev_hf ON prev_phf.`health_facility_id` = prev_hf.`id`
            LEFT JOIN `patient_chart` pc_ipc ON pc_ipc.`id` = (
                SELECT 
                    pc_ipc1.`id` 
                FROM `patient_chart` pc_ipc1
                LEFT JOIN `chart_type` ct_ipc1 ON pc_ipc1.`chart_type_id` = ct_ipc1.`id`
                LEFT JOIN `chart_status` cs_ipc1 ON pc_ipc1.`chart_status_id` = cs_ipc1.`id`
                WHERE ct_ipc1.`description` = 'Intro Phone Call'
                AND pc_ipc1.`chart_status_id` IS NOT NULL
                AND pc_ipc1.`patient_id` = p.`id`
                AND pc_ipc1.`is_deleted` = 0
                ORDER BY pc_ipc1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_ipc ON pc_ipc.`chart_type_id` = ct_ipc.`id`
            LEFT JOIN `chart_status` cs_ipc ON pc_ipc.`chart_status_id` = cs_ipc.`id`
            LEFT JOIN `patient_chart` pc_hsat ON pc_hsat.`id` = (
                SELECT 
                    pc_hsat1.`id` 
                FROM `patient_chart` pc_hsat1
                LEFT JOIN `chart_type` ct_hsat1 ON pc_hsat1.`chart_type_id` = ct_hsat1.`id`
                LEFT JOIN `chart_status` cs_hsat1 ON pc_hsat1.`chart_status_id` = cs_hsat1.`id`
                WHERE ct_hsat1.`description` IN (
                    'HSAT',
                    'HSAT (Mobile)',
                    'HSAT Curbside'
                )
                AND pc_hsat1.`chart_status_id` IS NOT NULL
                AND pc_hsat1.`patient_id` = p.`id`
                AND pc_hsat1.`is_deleted` = 0
                ORDER BY pc_hsat1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_hsat ON pc_hsat.`chart_type_id` = ct_hsat.`id`
            LEFT JOIN `chart_status` cs_hsat ON pc_hsat.`chart_status_id` = cs_hsat.`id`
            LEFT JOIN `appointment_notice_fax` anf ON anf.`id` = (
                SELECT anf1.`id`
                FROM `appointment_notice_fax` anf1
                WHERE anf1.`patient_id` = p.`id`
                AND anf1.`is_deleted` = 0
                ORDER BY anf1.`updated_at` DESC
                LIMIT 1
            )
            LEFT JOIN `fax_tran_outbound` fto ON fto.`appointment_notice_fax_id` = anf.`id`
            LEFT JOIN `patient_chart` pc_hrd ON pc_hrd.`id` = (
                SELECT 
                    pc_hrd1.`id` 
                FROM `patient_chart` pc_hrd1
                LEFT JOIN `chart_type` ct_hrd1 ON pc_hrd1.`chart_type_id` = ct_hrd1.`id`
                LEFT JOIN `chart_status` cs_hrd1 ON pc_hrd1.`chart_status_id` = cs_hrd1.`id`
                WHERE ct_hrd1.`description` = 'HSAT Repeat'
                AND pc_hrd1.`chart_status_id` IS NOT NULL
                AND pc_hrd1.`patient_id` = p.`id`
                AND pc_hrd1.`is_deleted` = 0
                ORDER BY pc_hrd1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_hrd ON pc_hrd.`chart_type_id` = ct_hrd.`id`
            LEFT JOIN `chart_status` cs_hrd ON pc_hrd.`chart_status_id` = cs_hrd.`id`          
            LEFT JOIN `patient_chart` pc_hri ON pc_hri.`id` = (
                SELECT 
                    pc_hri1.`id` 
                FROM `patient_chart` pc_hri1
                LEFT JOIN `chart_type` ct_hri1 ON pc_hri1.`chart_type_id` = ct_hri1.`id`
                LEFT JOIN `chart_status` cs_hri1 ON pc_hri1.`chart_status_id` = cs_hri1.`id`
                WHERE ct_hri1.`description` = 'HSAT Results Interpretation'
                AND pc_hri1.`chart_status_id` IS NOT NULL
                AND pc_hri1.`patient_id` = p.`id`
                AND pc_hri1.`is_deleted` = 0
                ORDER BY pc_hri1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_hri ON pc_hri.`chart_type_id` = ct_hri.`id`
            LEFT JOIN `chart_status` cs_hri ON pc_hri.`chart_status_id` = cs_hri.`id`
            LEFT JOIN `patient_chart` pc_pre ON pc_pre.`id` = (
                SELECT 
                    pc_pre1.`id` 
                FROM `patient_chart` pc_pre1
                LEFT JOIN `chart_type` ct_pre1 ON pc_pre1.`chart_type_id` = ct_pre1.`id`
                LEFT JOIN `chart_status` cs_pre1 ON pc_pre1.`chart_status_id` = cs_pre1.`id`
                WHERE ct_pre1.`description` = 'Prescription'
                AND pc_pre1.`chart_status_id` IS NOT NULL
                AND pc_pre1.`patient_id` = p.`id`
                AND pc_pre1.`is_deleted` = 0
                ORDER BY pc_pre1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_pre ON pc_pre.`chart_type_id` = ct_pre.`id`
            LEFT JOIN `chart_status` cs_pre ON pc_pre.`chart_status_id` = cs_pre.`id`
            LEFT JOIN `patient_chart` pc_quo ON pc_quo.`id` = (
                SELECT 
                    pc_quo1.`id` 
                FROM `patient_chart` pc_quo1
                LEFT JOIN `chart_type` ct_quo1 ON pc_quo1.`chart_type_id` = ct_quo1.`id`
                LEFT JOIN `chart_status` cs_quo1 ON pc_quo1.`chart_status_id` = cs_quo1.`id`
                WHERE ct_quo1.`description` = 'Quotation'
                AND pc_quo1.`chart_status_id` IS NOT NULL
                AND pc_quo1.`patient_id` = p.`id`
                AND pc_quo1.`is_deleted` = 0
                ORDER BY pc_quo1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_quo ON pc_quo.`chart_type_id` = ct_quo.`id`
            LEFT JOIN `chart_status` cs_quo ON pc_quo.`chart_status_id` = cs_quo.`id`
            LEFT JOIN `patient_insurance` pin ON pin.`id` = (
                SELECT pin1.`id`
                FROM `patient_insurance` pin1
                WHERE pin1.`is_deleted` = 0
                AND p.`id` = pin1.`patient_id`
                AND pin1.`start_date` <= :now
                AND (pin1.`end_date` IS NULL OR pin1.`end_date` > :now)
                LIMIT 1
            )
            LEFT JOIN `insurance` i ON i.`id` = pin.`insurance_id`
            LEFT JOIN `patient_chart` pc_cpap_t ON pc_cpap_t.`id` = (
                SELECT 
                    pc_cpap_t1.`id` 
                FROM `patient_chart` pc_cpap_t1
                LEFT JOIN `chart_type` ct_cpap_t1 ON pc_cpap_t1.`chart_type_id` = ct_cpap_t1.`id`
                LEFT JOIN `chart_status` cs_cpap_t1 ON pc_cpap_t1.`chart_status_id` = cs_cpap_t1.`id`
                WHERE ct_cpap_t1.`description` = 'CPAP Eval'
                AND ct_cpap_t1.`description` = 'CPAP End Eval'
                AND pc_cpap_t1.`chart_status_id` IS NOT NULL
                AND pc_cpap_t1.`patient_id` = p.`id`
                AND pc_cpap_t1.`is_deleted` = 0
                ORDER BY pc_cpap_t1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_cpap_t ON pc_cpap_t.`chart_type_id` = ct_cpap_t.`id`
            LEFT JOIN `chart_status` cs_cpap_t ON pc_cpap_t.`chart_status_id` = cs_cpap_t.`id`
            LEFT JOIN `patient_chart` pc_cpap_ss ON pc_cpap_ss.`id` = (
                SELECT 
                    pc_cpap_ss1.`id` 
                FROM `patient_chart` pc_cpap_ss1
                LEFT JOIN `chart_type` ct_cpap_ss1 ON pc_cpap_ss1.`chart_type_id` = ct_cpap_ss1.`id`
                LEFT JOIN `chart_status` cs_cpap_ss1 ON pc_cpap_ss1.`chart_status_id` = cs_cpap_ss1.`id`
                WHERE ct_cpap_ss1.`description` = 'CPAP Setup'
                AND ct_cpap_ss1.`description` = 'CPAP Set Up (Mobile)'
                AND ct_cpap_ss1.`description` = 'CPAP Setup Sold'
                AND ct_cpap_ss1.`description` = 'CPAP Setup - Telehealth'
                AND pc_cpap_ss1.`chart_status_id` IS NOT NULL
                AND pc_cpap_ss1.`patient_id` = p.`id`
                AND pc_cpap_ss1.`is_deleted` = 0
                ORDER BY pc_cpap_ss1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_cpap_ss ON pc_cpap_ss.`chart_type_id` = ct_cpap_ss.`id`
            LEFT JOIN `chart_status` cs_cpap_ss ON pc_cpap_ss.`chart_status_id` = cs_cpap_ss.`id`
            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` ASC
                LIMIT 1
            )
            LEFT JOIN `patient_chart` pc_lcc ON pc_lcc.`id` = (
                SELECT 
                    pc_lcc1.`id` 
                FROM `patient_chart` pc_lcc1
                LEFT JOIN `chart_type` ct_lcc1 ON pc_lcc1.`chart_type_id` = ct_lcc1.`id`
                LEFT JOIN `chart_status` cs_lcc1 ON pc_lcc1.`chart_status_id` = cs_lcc1.`id`
                WHERE ct_lcc1.`description` = 'Care Call'
                AND pc_lcc1.`chart_status_id` IS NOT NULL
                AND pc_lcc1.`patient_id` = p.`id`
                AND pc_lcc1.`is_deleted` = 0
                ORDER BY pc_lcc1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_lcc ON pc_lcc.`chart_type_id` = ct_lcc.`id`
            LEFT JOIN `chart_status` cs_lcc ON pc_lcc.`chart_status_id` = cs_lcc.`id`
            LEFT JOIN `patient_chart` pc_six_ff ON pc_six_ff.`id` = (
                SELECT 
                    pc_six_ff1.`id` 
                FROM `patient_chart` pc_six_ff1
                LEFT JOIN `chart_type` ct_six_ff1 ON pc_six_ff1.`chart_type_id` = ct_six_ff1.`id`
                LEFT JOIN `chart_status` cs_six_ff1 ON pc_six_ff1.`chart_status_id` = cs_six_ff1.`id`
                WHERE ct_six_ff1.`description` = '6 Month Follow-up'
                AND pc_six_ff1.`chart_status_id` IS NOT NULL
                AND pc_six_ff1.`patient_id` = p.`id`
                AND pc_six_ff1.`is_deleted` = 0
                ORDER BY pc_six_ff1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_six_ff ON pc_six_ff.`chart_type_id` = ct_six_ff.`id`
            LEFT JOIN `chart_status` cs_six_ff ON pc_six_ff.`chart_status_id` = cs_six_ff.`id`
            LEFT JOIN `patient_chart` pc_six_ff_latest ON pc_six_ff_latest.`id` = (
                SELECT 
                    pc_six_ff_latest1.`id` 
                FROM `patient_chart` pc_six_ff_latest1
                LEFT JOIN `chart_type` ct_six_ff_latest1 ON pc_six_ff_latest1.`chart_type_id` = ct_six_ff_latest1.`id`
                LEFT JOIN `chart_status` cs_six_ff_latest1 ON pc_six_ff_latest1.`chart_status_id` = cs_six_ff_latest1.`id`
                WHERE ct_six_ff_latest1.`description` = '6 Month Follow-up'
                AND pc_six_ff_latest1.`chart_status_id` IS NOT NULL
                AND pc_six_ff_latest1.`patient_id` = p.`id`
                AND pc_six_ff_latest1.`is_deleted` = 0
                ORDER BY pc_six_ff_latest1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `patient_billing` pb_latest ON pb_latest.`id` = (
                SELECT 
                    pb2.`id`
                FROM `patient_billing` pb2
                LEFT JOIN `patient` pat ON pb2.`patient_id` = pat.`id`
                LEFT JOIN `health_facility` hf ON pb2.`health_facility_id` = hf.`id` 
                LEFT JOIN `patient_insurance` pi ON pb2.`patient_insurance_id` = pi.`id`
                LEFT JOIN `insurance` i ON pi.`insurance_id` = i.`id`
                WHERE pb2.`patient_id` = p.`id`
                AND pb2.`billing_status` != 'Void'
                ORDER BY pb2.`billing_date` ASC
                LIMIT 1
            )
            LEFT JOIN `referral` r ON r.`id` = (
                SELECT r1.`id`
                FROM `referral` r1
                WHERE r1.`id` = p.`referral_id`
                AND r1.`is_deleted` = 0
                LIMIT 1
            )
            LEFT JOIN `fax_tran` ftrn ON ftrn.`id` = r.`fax_tran_id`
            LEFT JOIN `patient_file` pf ON pf.`id` = (
                SELECT pf1.`id`
                FROM `patient_file` pf1
                LEFT JOIN `file_type` ft ON ft.`id` = pf1.`file_type_id`
                WHERE pf1.`is_deleted` = 0
                AND ft.`description` IN (
                    'Mild Sleep Study Result',
                    'Moderate Sleep Study Result',
                    'Severe Sleep Study Result',
                    'Very Mild Sleep Study Result',
                    'Very Severe Sleep Study Result',
                    'Negative OSA'
                )
                AND pf1.`patient_id` = p.`id`
                ORDER BY pf1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `file_type` ft ON ft.`id` = pf.`file_type_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.`id` IS NOT NULL
            AND hf.`description` IS NOT NULL
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            ORDER BY p.`referral_date` DESC
  
        ";



        $stmtParams['now'] = date('Y-m-d');
        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;

    }

    public function overviewHealthFacility($patientId) {
        $sql = "
          SELECT phf.`health_facility_id`, hf.`description`
          FROM `patient_health_facility` phf
          LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
          WHERE phf.`is_deleted` = 0
          AND phf.`start_date` IS NOT NULL
          AND phf.`start_date` != ''
          AND (phf.`end_date` IS NULL OR phf.`end_date` > :now)
          AND phf.`patient_id` = :patientId
          ORDER BY phf.`start_date` DESC
          LIMIT 1
        ";

        $stmtParams['now'] = date('Y-m-d');
        $stmtParams['patientId'] = $patientId;
        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $result = $query->fetch();

        return $result;
    }


    /* ============================================================= */
    /* ================== ZAP SYS QUERY MODULE ===================== */
    /* ============================================================= */

    public function rx_patient_list($userId, $searchText = null){
        $stmtParams = array();
        $sqlWhere = "WHERE p.`is_deleted` = 0 AND p.`full_name` IS NOT NULL AND p.`full_name` != ' ' ";

        if(null !== $searchText && 'null' !== $searchText){
            $sqlWhere .= " AND p.`full_name` LIKE :searchTxt ";
            $stmtParams['searchTxt'] = "%" . $searchText . "%";
        }

        $sql = "SELECT 
                    p.`id`,
                    p.`full_name` as patient_fullName,
                    p.`address` as patient_description,
                    p.`address`,
                    p.`gender`,
                    DATE_FORMAT(p.`birth_date`, '%m/%d/%Y') as birthDate,
                    TIMESTAMPDIFF(YEAR, p.`birth_date`, CURDATE()) AS age,
                    CONCAT_WS(', ', p.`email`, p.`email_two`, p.`email_three`) AS emails,
                    CONCAT_WS(', ', p.`mobile_no`, p.`mobile_no_two`, p.`mobile_no_three`) AS mobileNos,
                    CONCAT_WS(', ', p.`phone_no`, p.`phone_no_two`, p.`phone_no_three`) AS phoneNos
                FROM `patient` p 
                $sqlWhere
                ORDER BY p.`id` DESC
                LIMIT 20 ";

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

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

    public function rx_patient_billing($userId, $patientId){

        $columns = array(
            array('pb.`billing_date`',  "DATE_FORMAT(pb.`billing_date`, '%m/%d/%Y')", 'billing_date'),
            array('pb.`invoice_no`', 'pb.`invoice_no`'),
            array('hf.`description`', 'hf.`description`', 'branch_desc'),
            array('hf2.`description`', 'hf2.`description`', 'branch_address_desc'),
            array('pb.`payer_type`', 'pb.`payer_type`', 'payer'),
            array('pb.`amount_due`', 'pb.`amount_due`'),
            array('pb.`amount_balance`', 'pb.`amount_balance`', 'balance'),
        );

        $select = "SELECT";
        $from = "FROM `patient_billing` pb";
        $joins = " LEFT JOIN `health_facility` hf ON pb.`health_facility_id` = hf.`id`";
        $joins .= " LEFT JOIN `health_facility` hf2 ON pb.`health_facility_for_address_id` = hf2.`id`";
        $sqlWhere = "WHERE pb.`patient_id` = :patient_id";
        $sqlWhere .= " AND pb.`is_deleted` = 0";
        $groupBy = "";
        $orderBy = " ORDER BY pb.`created_at` DESC ";
        $stmtParams = array(
            'patient_id' => $patientId
        );

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


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

}
