<?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'),
            array('prf.`id`', "(
            	SELECT
					CASE
						WHEN prf.`is_green_flag` THEN 'green'
						WHEN prf.`is_yellow_flag` THEN 'yellow'
						WHEN prf.`is_red_flag` THEN 'red'
						WHEN prf.`is_blue_flag` THEN 'blue'
						WHEN prf.`is_purple` THEN 'purple'
						WHEN prf.`is_orange` THEN 'orange'
						WHEN prf.`is_light_blue` THEN 'lightblue'
						WHEN prf.`is_pink` THEN 'pink'
						ELSE '#2199e8'
					END AS color
				FROM patient_red_flag prf
				WHERE prf.patient_id = p.id
				AND prf.`is_deleted` = 0
				AND :now >= prf.`start_date`
				AND (:now <= prf.`end_date` OR DATE_FORMAT(prf.`end_date`, '%m/%d/%Y') IS NULL)
				LIMIT 1
            )", 'flagColor'),
        );
        $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 prf1.`id`
                    FROM `patient_red_flag` prf1
                    WHERE prf1.`patient_id` = p.`id`
                    AND prf1.`start_date` IS NOT NULL
                    AND prf1.`end_date` IS NULL
                    ORDER BY prf1.`start_date` DESC
                    LIMIT 1
                )";
        $sqlWhere = "WHERE p.`is_deleted` = 0";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array(
			'now' => date('Y-m-d'),
		);
        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'];
        }
        if(!empty($get['flagColor'])){
            switch ($get['flagColor']) {
                case 'red':
                    $sqlWhere .= " AND (prf.`is_red_flag` = 1)";
                    break;
                case 'blue':
                    $sqlWhere .= " AND (prf.`is_blue_flag` = 1)";
                    break;
                case 'green':
                    $sqlWhere .= " AND (prf.`is_green_flag` = 1)";
                    break;
                case 'yellow':
                    $sqlWhere .= " AND (prf.`is_yellow_flag` = 1)";
                    break;
                case 'purple':
                    $sqlWhere .= " AND (prf.`is_purple` = 1)";
                    break;
                case 'orange':
                    $sqlWhere .= " AND (prf.`is_orange` = 1)";
                    break;
                case 'light_blue':
                    $sqlWhere .= " AND (prf.`is_light_blue` = 1)";
                    break;
                case 'pink':
                    $sqlWhere .= " AND (prf.`is_pink` = 1)";
                    break;
            }
            $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(isset($params['city']) && !empty($params['city']) && 'NULL' !== strtoupper($params['city'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "p.`city_id` = :city";
            $stmtParams['city'] = is_numeric($params['city']) ? $params['city'] : base64_decode($params['city']);
        }

        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,
                CONCAT_WS(' / ', p.`phone_no`, p.`phone_no_two`, p.`phone_no_three`) AS phoneNo,
                CONCAT_WS(' / ', p.`mobile_no`, p.`mobile_no_two`, p.`mobile_no_three`) AS mobileNo,
                CONCAT_WS(' / ', p.`email`, p.`email_two`, p.`email_three`) AS email,
                ct.`description` AS cityDesc,
                provi.`description` patientProvince,
                post.`description` postalCode,
                hf.`description` patientBranch,
                co.`description` AS companyDesc,
                rp.`full_name` AS referringProvider,
                rhf.`description` AS referringHealthFacility,
                pc.`full_name` AS primaryClinician,
                i.`description` AS patientInsurance,
                p.`is_active`
            FROM `patient` p
            LEFT JOIN `account` a ON p.`account_id` = a.`id`
            LEFT JOIN `provider` pc ON pc.`id` = p.`primary_provider_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.`start_date` <= :now
              AND (prhf1.`end_date` IS NULL OR prhf1.`end_date` > :now)
			  ORDER BY prhf1.`start_date` DESC
			  LIMIT 1
			)
            LEFT JOIN `referring_health_facility` rhf ON prhf.`referring_health_facility_id` = rhf.`id`
			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.`start_date` <= :now
              AND (prp1.`end_date` IS NULL OR prp1.`end_date` > :now)
			  ORDER BY prp1.`start_date` DESC
			  LIMIT 1
			)
			LEFT JOIN `referring_provider` rp ON prp.`referring_provider_id` = rp.`id`
		    $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
        ";

        $stmtParams['now'] = date('Y-m-d');
        $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, $userData) {
        $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;
        }

        if(!is_null($userData) && !empty($userData)){
            if($userData['type'] != 'Super Admin'){

                $andWhere .= " 
                        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');
            }
        }

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

        if(!is_null($userData) && !empty($userData)){
            if($userData['type'] != 'Super Admin'){

                $andWhere .= " 
                        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');
            }
        }

        $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, $userData) {

        $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'];
        }*/

        if(!is_null($userData) && !empty($userData)){
            if($userData['type'] != 'Super Admin'){

                $andWhere .= " 
                        AND EXISTS (
                            SELECT uc.`id`
                            FROM `account_user_company` uc
                            WHERE uc.`account_user_id` = :userId
                            AND uc.`company_id` = hf2.`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');
            }
        }

        $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 `patient_health_facility` phf ON phf.`id` = (
              SELECT phf2.`id`
              FROM `patient_health_facility` phf2
              WHERE phf2.`patient_id` = s.`patient_id`
              AND phf2.`end_date` IS NULL
              AND phf2.`is_deleted` = 0
              ORDER BY phf2.`start_date` DESC
              LIMIT 1
            )
            LEFT JOIN `health_facility` hf2 ON phf.`health_facility_id` = hf2.`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, $userData) {

        $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'];
        }*/

        if(!is_null($userData) && !empty($userData)){
            if($userData['type'] != 'Super Admin'){

                $andWhere .= " 
                        AND EXISTS (
                            SELECT uc.`id`
                            FROM `account_user_company` uc
                            WHERE uc.`account_user_id` = :userId
                            AND uc.`company_id` = hf2.`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');
            }
        }

        $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 `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` hf2 ON phf.`health_facility_id` = hf2.`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 reportPatientCycle2($params) {
        $sqlWhere = "";
        $sqlWhere2 = "";
        $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;
                }
                $sqlWhere2 .= " 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;
                }
                $sqlWhere2 .= " AND ($hf_ors) ";
            }
        }

        if(isset($params['referringHealthFacility']) && !empty($params['referringHealthFacility']) && 'NULL' !== strtoupper($params['referringHealthFacility'])){
            $referringHealthFacilities = explode(',' , $params['referringHealthFacility']);
            if(count($referringHealthFacilities) > 0){
                $rhf_ors = '';
                foreach($referringHealthFacilities as $key => $referringHealthFacilityId){
                    $rhf_ors .= (empty($rhf_ors) ? '' : ' OR ') . "rhf.`id` = :referringHealthFacility_id_$key";
                    $stmtParams["referringHealthFacility_id_$key"] = $referringHealthFacilityId;
                }
                $sqlWhere2 .= " AND ($rhf_ors) ";
            }
        }

        if(isset($params['referringProvider']) && !empty($params['referringProvider']) && 'NULL' !== strtoupper($params['referringProvider'])){
            $referringProviders = explode(',' , $params['referringProvider']);
            if(count($referringProviders) > 0){
                $rp_ors = '';
                foreach($referringProviders as $key => $referringProviderId){
                    $rp_ors .= (empty($rp_ors) ? '' : ' OR ') . "rp.`id` = :referringProvider_id_$key";
                    $stmtParams["referringProvider_id_$key"] = $referringProviderId;
                }
                $sqlWhere2 .= " AND ($rp_ors) ";
            }
        }

	    $andWhereServices = '';
        if(isset($params['service']) && !empty($params['service']) && 'NULL' !== strtoupper($params['service'])){
            $services = explode(',' , $params['service']);
            if(count($services) > 0){
                $s_ors = '';
                foreach($services as $key => $serviceId){
                    $s_ors .= (empty($s_ors) ? '' : ' OR ') . "ps.`service_id` = :serviceId_$key";
                    $stmtParams["serviceId_$key"] = $serviceId;
                }
                $sqlWhere2 .= " AND ($s_ors) "; //AND temp_ps.patientServices IS NOT NULL
	            $andWhereServices .= " AND ($s_ors) ";
            }
        }

        if(isset($params['primary_provider']) && !empty($params['primary_provider']) && 'NULL' !== strtoupper($params['primary_provider'])){
            $primary_providers = explode(',' , $params['primary_provider']);
            if(count($primary_providers) > 0){
                $s_ors = '';
                foreach($primary_providers as $key => $primary_providersId){
                    $s_ors .= (empty($s_ors) ? '' : ' OR ') . "pc_cl.`provider_id` = :primary_providersId_$key";
                    $stmtParams["primary_providersId_$key"] = $primary_providersId;
                }
                $sqlWhere2 .= " AND ($s_ors) ";
            }
        }

        if(isset($params['is_due']) && !empty($params['is_due']) && 'NULL' !== strtoupper($params['is_due'])){
            $sqlWhere2 .= " AND (pc_hsat.`id` IS NULL OR
                                pc_hrd.`id` IS NULL OR
                                pc_hri.`id` IS NULL OR
                                pc_pft.`id` IS NULL OR
                                pc_pre.`id` IS NULL OR
                                pc_cs.`id` IS NULL)
            ";
        }

        /*
        if(!is_null($userData) && !empty($userData)){
            if($userData['type'] != 'Super Admin'){
                $sqlWhere2 .= " 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($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) ";
                    }
                }
            }
        }
        */

        if(isset($params['chartType']) && !empty($params['chartType']) && 'NULL' !== strtoupper($params['chartType'])){
            switch ($params['chartType']) {
                case 'Fax Count':
                    $sqlWhere2 .= " AND ftrn.`fax_category` = 'Referral'";
                    break;
                case 'HSAT':

                    /*if ($params['chartStatus'] == 'Unbooked') {
	                    $sqlWhere2 .= " 
                            AND ps_hsat.`service_id` = 1
                            AND NOT EXISTS(
                                SELECT *
                                FROM `patient_chart` pc
                                LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
                                LEFT JOIN `chart_type_group` ctg ON ctg.`id` = ct.`chart_type_group_id`
                                WHERE pc.`is_deleted` = 0
                                AND p.`id` = pc.`patient_id`
                                AND ctg.`description` = 'HSAT'
                        )";
                    } else {
	                    $sqlWhere2 .= " AND cs_hsat.`description` = :param";
                    }*/
	                $sqlWhere2 .= " AND IFNULL(cs_hsat.`description`,  'Unbooked') = :param";

	                break;
                case 'HSAT Repeat':

                    $sqlWhere2 .= " AND IFNULL(cs_hrd.`description`,  'Unbooked') = :param";

                    break;
                case 'HSAT Results Interpretation':

                    $sqlWhere2 .= " AND IFNULL(cs_hri.`description`,  'Unbooked') = :param";

                    break;
                case 'Prescription':

                    $sqlWhere2 .= " AND IFNULL(cs_pre.`description`,  'Unbooked') = :param";

                    break;
                case 'CPAP Setup':

                    $sqlWhere2 .= " AND IFNULL(cs_cs.`description`,  'Unbooked') = :param";

                    break;
                case 'Invoice':

                    $sqlWhere2 .= " AND IF(pb.`invoice_no`, 'Completed', 'Unbooked') = :param";

                    break;
                default:
                    break;
            }

            $stmtParams['param'] = $params['chartStatus'];

        }

        if(isset($params['fileType']) && !empty($params['fileType']) && 'NULL' !== strtoupper($params['fileType'])){
            $fileTypes = explode(',' , $params['fileType']);
            if(count($fileTypes) > 0){
                $ft_ors = '';
                foreach($fileTypes as $key => $fileTypeId){
                    $ft_ors .= (empty($ft_ors) ? '' : ' OR ') . "pf.`file_type_id` = :fileType_id_$key";
                    $stmtParams["fileType_id_$key"] = $fileTypeId;
                }
                $sqlWhere2 .= " AND ($ft_ors) ";
            }
        }

        $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 Acknowledgement (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
        ";

        $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.`id` AS faxId,
                ftrn.`fax_category`,
                IF(ftrn.`fax_category` = 'Referral', 'Referral', '') as isReferral,
                ftrn.`remote_id` AS receivedFax,
                p.`full_name`,
                pcl.`full_name` AS primaryClinician,
                rhf.`description` AS referringHealthFacility,
                rp.`full_name` AS referringProvider,
                temp_ps.`patientServices`,
                hf.`id` AS healthFacilityId,
                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,
                TO_BASE64(pc_ipc.`id`) AS ipcChartIdEncoded,
                DATE_FORMAT(pc_ipc.`chart_date`, '%m/%d/%Y') AS ipcChartDate,
                ct_ipc.`description` AS ipcChartType,
                cs_ipc.`description` AS ipcChartStatus,
                TO_BASE64(pc_hsat.`id`) AS hsatChartIdEncoded,
                DATE_FORMAT(pc_hsat.`chart_date`, '%m/%d/%Y') AS hsatChartDate,
                pc_hsat.`is_repeat` AS isHsatRepeat,
                ct_hsat.`description` AS hsatChartType,
                -- cs_hsat.`description` AS hsatChartStatus,
                IFNULL(cs_hsat.`description`,  'Unbooked') AS hsatChartStatus,
                TO_BASE64(pc_hrd.`id`) AS hrdChartIdEncoded,
                DATE_FORMAT(pc_hrd.`chart_date`, '%m/%d/%Y') AS hrdChartDate,
                ct_hrd.`description` AS hrdChartType,
                -- cs_hrd.`description` AS hrdChartStatus,
                IFNULL(cs_hrd.`description`,  'Unbooked') AS hrdChartStatus,
                TO_BASE64(pc_hri.`id`) AS hriChartIdEncoded,
                DATE_FORMAT(pc_hri.`chart_date`, '%m/%d/%Y') AS hriChartDate,
                ct_hri.`description` AS hriChartType,
                -- cs_hri.`description` AS hriChartStatus,
                IFNULL(cs_hri.`description`,  'Unbooked') AS hriChartStatus,
                TO_BASE64(pc_pft.`id`) AS pftChartIdEncoded,
                DATE_FORMAT(pc_pft.`chart_date`, '%m/%d/%Y') AS pftChartDate,
                ct_pft.`description` AS pftChartType,
                -- cs_pft.`description` AS pftChartStatus,
                IFNULL(cs_pft.`description`,  'Unbooked') AS pftChartStatus,
                TO_BASE64(pc_pre.`id`) AS preChartIdEncoded,
                DATE_FORMAT(pc_pre.`chart_date`, '%m/%d/%Y') AS preChartDate,
                ct_pre.`description` AS preChartType,
                -- cs_pre.`description` AS preChartStatus,
                IFNULL(cs_pre.`description`,  'Unbooked') AS preChartStatus,
                DATE_FORMAT(pc_pres.`chart_date`, '%m/%d/%Y') AS signedPrescriptionDate,
                ct_pres.`description` AS presChartType,
                cs_pres.`description` AS presChartStatus,
                TO_BASE64(pc_pres.`id`) AS signedPrescriptionId,
                TO_BASE64(pl.`id`) AS latestLetterSentIdEncoded,
                pl.`letter_type` AS latestLetterSent,
                TO_BASE64(pl.`id`) AS latestLetterSentIdEncoded,
                TO_BASE64(pc_cs.`id`) AS cpapSetupIdEncoded,
                DATE_FORMAT(pc_cs.`chart_date`, '%m/%d/%Y') AS cpapSetupDate,
                ct_cs.`description` AS cpapSetupType,
                -- cs_cs.`description` AS cpapSetupStatus,
                IFNULL(cs_cs.`description`,  'Unbooked') AS cpapSetupStatus,
                TO_BASE64(pc_six.`id`) AS sixMoFFIdEncoded,
                DATE_FORMAT(pc_six.`chart_date`, '%m/%d/%Y') AS sixMoFFDate,
                ct_six.`description` AS sixMoFFType,
                cs_six.`description` AS sixMoFFStatus,
                IF(pc_six.`chart_date` IS NOT NULL, 
                  DATE_FORMAT(DATE_ADD(pc_cs.`chart_date`, INTERVAL 6 MONTH), '%m/%d/%Y'), 
                  ''
                ) AS nextSixMonthsFF,
                temp_pf.`patientFiles`,
                pb.`invoice_no`,
                IF(pb.`invoice_no` IS NOT NULL, 'Completed',  'Unbooked') AS hasInvoice,
                temp_pbi.`patientBillingInventory`,
                IF(
                    (
                        SELECT pt.`id`
                        FROM `patient_ticket` pt
                        WHERE pt.`is_deleted` = 0
                        AND pt.`patient_id` = p.`id`
                        LIMIT 1
                ) IS NOT NULL, 'true', 'false') AS ticketExist,
                IF(
                    (
                        SELECT pn.`id`
                        FROM `patient_note` pn
                        WHERE pn.`is_deleted` = 0
                        AND pn.`patient_id` = p.`id`
                        LIMIT 1
                ) IS NOT NULL, 'true', 'false') AS patientNoteExist,
                IF(pc_hsat.`is_repeat` = 1, 1, 0) AS isHsatRepeatChecked, 
                IF(pc_hrd.`is_repeat` = 1, 1, 0) AS isHrdRepeatChecked, 
                IF(pc_hri.`is_repeat` = 1, 1, 0) AS isHriRepeatChecked, 
                IF(pc_pft.`is_repeat` = 1, 1, 0) AS isPftRepeatChecked, 
                IF(pc_pre.`is_repeat` = 1, 1, 0) AS isPreRepeatChecked, 
                IF(pc_cs.`is_repeat` = 1, 1, 0) AS isCsRepeatChecked
            FROM `patient` p
            LEFT JOIN `patient_chart` pc_cl ON pc_cl.`id` = (
                SELECT pc_cl1.`id`
                FROM `patient_chart` pc_cl1
                LEFT JOIN `chart_type` ct_cl1 ON pc_cl1.`chart_type_id` = ct_cl1.`id`
                LEFT JOIN `chart_type_group` cl_ctg ON ct_cl1.`chart_type_group_id` = cl_ctg.`id`
                WHERE pc_cl1.`is_deleted` = 0
                AND p.`id` = pc_cl1.`patient_id`
                AND cl_ctg.`description` IN (
                    'HSAT',
                    'HSAT Results Interpretation'
                )
                ORDER BY pc_cl1.`chart_date` DESC
                LIMIT 1 
            )
            LEFT JOIN `provider` pcl ON pcl.`id` = pc_cl.`provider_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 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 `patient_service` ps ON ps.`patient_id` = p.`id`
            LEFT JOIN `patient_service` ps_hsat ON ps_hsat.`id` = (
                SELECT
                    ps_hsat1.`id`
                FROM `patient_service` ps_hsat1
                LEFT JOIN `service` ser ON ps_hsat1.`service_id` = ser.`id`
                WHERE ps_hsat1.`is_deleted` = 0
                AND ps_hsat1.`patient_id` = p.`id`
                AND ser.`description` = 'Home Sleep Apnea Test (HSAT)'
                LIMIT 1
            )
            LEFT JOIN (
                SELECT
                    ps.`patient_id`,
                    GROUP_CONCAT(DISTINCT CONCAT_WS('|', ser.`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
                AND ser.is_deleted = 0
                AND ser.description IS NOT NULL 
                AND ser.description != '' 
                $andWhereServices
                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`
                LEFT JOIN `chart_type_group` ipc_ctg ON ct_ipc1.`chart_type_group_id` = ipc_ctg.`id`
                WHERE ipc_ctg.`description` = 'Pre-HSAT'
                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`
                LEFT JOIN `chart_type_group` hsat_ctg ON ct_hsat1.`chart_type_group_id` = hsat_ctg.`id`
                WHERE hsat_ctg.`description` = 'HSAT'
                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`
                LEFT JOIN `chart_type_group` hrd_ctg ON ct_hrd1.`chart_type_group_id` = hrd_ctg.`id`
                WHERE hrd_ctg.`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`
                LEFT JOIN `chart_type_group` hri_ctg ON ct_hri1.`chart_type_group_id` = hri_ctg.`id`
                WHERE hri_ctg.`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_pft ON pc_pft.`id` = (
                SELECT 
                    pc_pft1.`id` 
                FROM `patient_chart` pc_pft1
                LEFT JOIN `chart_type` ct_pft1 ON pc_pft1.`chart_type_id` = ct_pft1.`id`
                LEFT JOIN `chart_status` cs_pft1 ON pc_pft1.`chart_status_id` = cs_pft1.`id`
                LEFT JOIN `chart_type_group` pft_ctg ON ct_pft1.`chart_type_group_id` = pft_ctg.`id`
                WHERE pft_ctg.`description` = 'PFT'
                AND pc_pft1.`chart_status_id` IS NOT NULL
                AND pc_pft1.`patient_id` = p.`id`
                AND pc_pft1.`is_deleted` = 0
                ORDER BY pc_pft1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_pft ON pc_pft.`chart_type_id` = ct_pft.`id`
            LEFT JOIN `chart_status` cs_pft ON pc_pft.`chart_status_id` = cs_pft.`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`
                LEFT JOIN `chart_type_group` pre_ctg ON ct_pre1.`chart_type_group_id` = pre_ctg.`id`
                WHERE pre_ctg.`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_pres ON pc_pres.`id` = (
                SELECT 
                    pc_pres1.`id` 
                FROM `patient_chart` pc_pres1
                LEFT JOIN `chart_type` ct_pres1 ON pc_pres1.`chart_type_id` = ct_pres1.`id`
                LEFT JOIN `chart_status` cs_pres1 ON pc_pres1.`chart_status_id` = cs_pres1.`id`
                LEFT JOIN `chart_type_group` pres_ctg ON ct_pres1.`chart_type_group_id` = pres_ctg.`id`
                WHERE pres_ctg.`description` = 'Signed Prescription'
                AND pc_pres1.`chart_status_id` IS NOT NULL
                AND pc_pres1.`patient_id` = p.`id`
                AND pc_pres1.`is_deleted` = 0
                ORDER BY pc_pres1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_pres ON pc_pres.`chart_type_id` = ct_pres.`id`
            LEFT JOIN `chart_status` cs_pres ON pc_pres.`chart_status_id` = cs_pres.`id`
            LEFT JOIN `patient_letter` pl ON pl.`id` = (
                SELECT pl1.`id`
                FROM `patient_letter` pl1
                WHERE pl1.`is_deleted` = 0
                AND pl1.`patient_id` = p.`id`
                ORDER BY pl1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `patient_chart` pc_cs ON pc_cs.`id` = (
                SELECT 
                    pc_cs1.`id` 
                FROM `patient_chart` pc_cs1
                LEFT JOIN `chart_type` ct_cs1 ON pc_cs1.`chart_type_id` = ct_cs1.`id`
                LEFT JOIN `chart_status` cs_cs1 ON pc_cs1.`chart_status_id` = cs_cs1.`id`
                LEFT JOIN `chart_type_group` cs_ctg ON ct_cs1.`chart_type_group_id` = cs_ctg.`id`
                WHERE cs_ctg.`description` = 'CPAP Setup'
                AND pc_cs1.`chart_status_id` IS NOT NULL
                AND pc_cs1.`patient_id` = p.`id`
                AND pc_cs1.`is_deleted` = 0
                ORDER BY pc_cs1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_cs ON pc_cs.`chart_type_id` = ct_cs.`id`
            LEFT JOIN `chart_status` cs_cs ON pc_cs.`chart_status_id` = cs_cs.`id`
            LEFT JOIN `patient_chart` pc_six ON pc_six.`id` = (
                SELECT 
                    pc_six1.`id` 
                FROM `patient_chart` pc_six1
                LEFT JOIN `chart_type` ct_six1 ON pc_six1.`chart_type_id` = ct_six1.`id`
                LEFT JOIN `chart_status` cs_six1 ON pc_six1.`chart_status_id` = cs_six1.`id`
                LEFT JOIN `chart_type_group` six_ctg ON ct_six1.`chart_type_group_id` = six_ctg.`id`
                WHERE six_ctg.`description` = '6 Months Follow-up'
                AND pc_six1.`chart_status_id` IS NOT NULL
                AND pc_six1.`patient_id` = p.`id`
                AND pc_six1.`is_deleted` = 0
                ORDER BY pc_six1.`chart_date` DESC
                LIMIT 1
            )
            LEFT JOIN `chart_type` ct_six ON pc_six.`chart_type_id` = ct_six.`id`
            LEFT JOIN `chart_status` cs_six ON pc_six.`chart_status_id` = cs_six.`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` NOT IN(
                    'Void',
                    'Returned',
                    'Trial'
                )
                AND pb1.`id` IN
                (
                    SELECT pbb1.`patient_billing_id`
                    FROM `patient_billing_breakdown` pbb1
                    WHERE pbb1.`is_deleted` = 0
                    AND pb1.`id` = pbb1.`patient_billing_id`
                    AND pbb1.`item_type` = 'CPAP Package'
                )
                ORDER BY pb1.`billing_date` DESC
                LIMIT 1
            )
            LEFT JOIN (
                SELECT
                    pbi.`patient_billing_id`,
                    GROUP_CONCAT(CONCAT_WS('|', pbi.`id`, IF(isn.`serial_no` IS NOT NULL, isn.`serial_no`, IF(pbi.`serial_no`,pbi.`serial_no`, '')), IF(pbi.`is_trial` = 0, 'Sold', 'Trial')) SEPARATOR ',' ) AS patientBillingInventory
                FROM `patient_billing_inventory` pbi
                LEFT JOIN `item` i ON i.`id` = pbi.`item_id`
                LEFT JOIN `inventory_serial_no` isn ON isn.`patient_billing_inventory_id` = pbi.`id`
                WHERE isn.`serial_no` IS NOT NULL
                GROUP BY pbi.`patient_billing_id`
            ) temp_pbi ON temp_pbi.`patient_billing_id` = pb.`id`
            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 (
                SELECT
                    pf.`patient_id`,
                    GROUP_CONCAT(CONCAT_WS('|', pf.`id`, ft.`id`, ft.`description`) SEPARATOR ',' ) AS patientFiles
                FROM `patient_file` pf
                LEFT JOIN `file_type` ft ON pf.`file_type_id` = ft.`id`
                WHERE pf.`is_deleted` = 0
                AND ft.`description` IN(
                    'Mild Sleep Study Result',
                    'Moderate Sleep Study Result',
                    'Oximetry Result',
                    'PFT Result',
                    'PSG Result',
                    'Severe Sleep Study Result',
                    'Very Mild Sleep Study Result',
                    'Very Severe Sleep Study Result',
                    'Abnormal Sleep Study Report',
                    'Prescription',
                    'Moderate Sleep Study Result'
                )
                GROUP BY pf.`patient_id`
            ) temp_pf ON temp_pf.`patient_id` = p.`id`
            LEFT JOIN `patient_file` pf ON pf.`patient_id` = p.`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.`is_deleted` = 0
            AND hf.`description` IS NOT NULL
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
            -- AND temp_ps.`patientServices` IS NOT NULL
            $sqlWhere2
            GROUP BY p.`id`
            ORDER BY p.`referral_date` DESC
        ";
		//echo'<pre>';die(var_dump($sql));
        $stmtParams['now'] = date('Y-m-d');
        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute($stmtParams);

        $result['patientCycle'] = $query->fetchAll();
		//die(print_r($result['patientCycle']));
        $result['summary'] = array(
            'cycle' => array(),
            'prescription' => array(
                array(),
                array(),
            ),
            'service' => array(),
            'service_branch' => array(),
            'prescriptionChartType' => array(
                'Prescription',
                'Signed Prescription',
            ),
            'cycleTypes' => array(
                'HSAT',
                'HSAT Repeat',
                'HSAT Results Interpretation',
                'Prescription',
                'CPAP Setup',
                'Invoice',
            ),
            'results' => array(),
            'purchase' => array(),
            'branch' => array(),
            'primaryClinician' => array()
        );

        //$result['unbooked_patients'] = [];


        foreach ($result['patientCycle'] as $key => $row) {
	        //PATIENT AFFILIATE
            if($row['healthFacility'] != null or $row['healthFacility'] != '') {
                if(isset($result['summary']['branch'][$row['healthFacility']])) {
                    $result['summary']['branch'][$row['healthFacility']]++;
                } else {
                    $result['summary']['branch'][$row['healthFacility']] = 1;
                }
            }

            //PATIENT PRIMARY CLINICIAN
            if($row['primaryClinician'] != null or $row['primaryClinician'] != '') {
                if(isset($result['summary']['primaryClinician'][$row['primaryClinician']])) {
                    $result['summary']['primaryClinician'][$row['primaryClinician']]++;
                } else {
                    $result['summary']['primaryClinician'][$row['primaryClinician']] = 1;
                }
            }
	        // </editor-fold >

            //PATIENT CYCLE
            if (!isset($result['summary']['cycle'][$row['healthFacility']])) {
                $result['summary']['cycle'][$row['healthFacility']] = array(
                    'branchId' => $row['healthFacilityId'],
                    'referral' => 0,
                    'faxCount' => 0,
                    'hsatReferrals' => 0,
                    'statuses' => array(),
                );
            }

            //CYCLE
            if(isset($result['summary']['cycle'][$row['healthFacility']]['referral'])) {
                $result['summary']['cycle'][$row['healthFacility']]['referral']++;
            } else {
                $result['summary']['cycle'][$row['healthFacility']]['referral'] = 1;
            }

            if(!empty($row['isReferral'])) {
                $result['summary']['cycle'][$row['healthFacility']]['faxCount']++;
            }

            // COUNT NUMBER OF PATIENT WITH HSAT SERVICE
            $hsatReferralSplit = explode('@@',$row['patientServices']);
	        $hsatFlag = 0; // IMPORTANT: This will be used on counting in HSAT CYCLE SUMMARY.
            foreach($hsatReferralSplit as $hrs) {
                $hsatReferrals = explode('|',$hrs);
                if(isset($hsatReferrals[1])) {
                    //if($hsatReferrals[1] == 'Home Sleep Apnea Test (HSAT)') {
                    if($hsatReferrals[0] == 1) {
                        $result['summary']['cycle'][$row['healthFacility']]['hsatReferrals']++;
	                    $hsatFlag = 1; // IF PATIENT HAS HSAT IN SERVICE THEN INCREMENT 1 ON CYCLE SUMMARY.
	                    break;
                    }
                }
            }


            // COUNT NUMBER OF REFERRAL PATIENT
            if(!empty($row['isReferral'])) {
                $result['summary']['cycle'][$row['healthFacility']]['faxCount']++;
            }

            $order = function ($status){
              switch ($status) {
                  case 'Completed':
                      return 1;
                      break;
                  case 'Booked':
                      return 2;
                      break;
                  case 'No Show':
                      return 9;
                      break;
                  case 'Cancelled':
                      return 10;
                      break;
                  case 'Unbooked':
                      return 11;
                      break;
                  default:
                      return 5;
                      break;
              }
            };

            $cycles = array(
                array(
                    //'status' => !empty($row['hsatChartStatus']) ? $row['hsatChartStatus'] : 'Unbooked',
                    //'order' => !empty($row['hsatChartStatus']) ? $order($row['hsatChartStatus']) : $order('Unbooked'),
                    'status' => $row['hsatChartStatus'],
                    'order' => $order($row['hsatChartStatus']),
                    'type' => 'HSAT',
                ),
                array(
                    //'status' => !empty($row['hrdChartStatus']) || $row['isHsatRepeat'] == false  ? $row['hrdChartStatus'] : 'Unbooked',
                    //'order' => !empty($row['hrdChartStatus']) || $row['isHsatRepeat'] == false ? $order($row['hrdChartStatus']) : $order('Unbooked'),
                    'status' => $row['isHsatRepeat'] == false  ? $row['hrdChartStatus'] : 'Unbooked',
                    'order' => $row['isHsatRepeat'] == false ? $order($row['hrdChartStatus']) : $order('Unbooked'),
                    'type' => 'HSAT Repeat',
                ),
                array(
                    //'status' => !empty($row['hriChartStatus']) ? $row['hriChartStatus'] : 'Unbooked',
                    //'order' => !empty($row['hriChartStatus']) ? $order($row['hriChartStatus']) : $order('Unbooked'),
                    'status' => $row['hriChartStatus'],
                    'order' => $order($row['hriChartStatus']),
                    'type' => 'HSAT Results Interpretation',
                ),
                array(
                    'status' => $row['preChartStatus'],
                    'order' => $order($row['preChartStatus']),
                    'type' => 'Prescription',
                ),
                array(
                    'status' => $row['cpapSetupStatus'],
                    'order' => $order($row['cpapSetupStatus']),
                    'type' => 'CPAP Setup',
                ),
                /*
                array(
                    'status' => $row['sixMoFFStatus'],
                    'order' => $order($row['sixMoFFStatus']),
                    'type' => '6 Months Follow-up',
                ),
                */
                array(
                    'status' => $row['hasInvoice'],
                    'order' => !empty($row['hasInvoice']) ? 1 : '',
                    'type' => 'Invoice',
                ),
            );
            foreach ($cycles as $cycle) {
                if(!empty($cycle['status'])) {
                    if (!isset($result['summary']['cycle'][$row['healthFacility']]['statuses'][$cycle['status']])) {
                        $result['summary']['cycle'][$row['healthFacility']]['statuses'][$cycle['status']] = array(
                            'order' => $cycle['order'],
                            'status' => $cycle['status'],
                            'types' => array()
                        );
                    }


                    // initialize count
                    if (!isset($result['summary']['cycle'][$row['healthFacility']]['statuses'][$cycle['status']]['types'][$cycle['type']])) {
	                    $result['summary']['cycle'][$row['healthFacility']]['statuses'][$cycle['status']]['types'][$cycle['type']] = 0;
                    }


                    if ($cycle['type'] === 'HSAT' && $cycle['status'] === 'Unbooked') {

	                    $result['summary']['cycle'][$row['healthFacility']]['statuses'][$cycle['status']]['types'][$cycle['type']] += $hsatFlag;

                    } else if($cycle['type'] === 'HSAT Repeat' && $cycle['status'] === 'Unbooked') {

                        $result['summary']['cycle'][$row['healthFacility']]['statuses'][$cycle['status']]['types'][$cycle['type']] += $hsatFlag;

                    } else if($cycle['type'] === 'HSAT Results Interpretation' && $cycle['status'] === 'Unbooked') {

                        $result['summary']['cycle'][$row['healthFacility']]['statuses'][$cycle['status']]['types'][$cycle['type']] += $hsatFlag;

                    } else if($cycle['type'] === 'Prescription' && $cycle['status'] === 'Unbooked') {

                        $result['summary']['cycle'][$row['healthFacility']]['statuses'][$cycle['status']]['types'][$cycle['type']] += $hsatFlag;

                    } else if($cycle['type'] === 'CPAP Setup' && $cycle['status'] === 'Unbooked') {

                        $result['summary']['cycle'][$row['healthFacility']]['statuses'][$cycle['status']]['types'][$cycle['type']] += $hsatFlag;

                    } else if($cycle['type'] === 'Invoice' && $cycle['status'] === 'Unbooked') {

                        $result['summary']['cycle'][$row['healthFacility']]['statuses'][$cycle['status']]['types'][$cycle['type']] += $hsatFlag;

                    } else {

	                    $result['summary']['cycle'][$row['healthFacility']]['statuses'][$cycle['status']]['types'][$cycle['type']] += $hsatFlag;

                    }

                }
            }
            //SERVICES
            $ps_split = explode('@@',$row['patientServices']);
            foreach($ps_split as $ps) {
                $patientServices = explode('|',$ps);
                if(isset($patientServices[1])) {
                    if(isset($result['summary']['service_branch'][$row['healthFacilityId'].'-'.$row['healthFacility']])) {
                        $result['summary']['service_branch'][$row['healthFacilityId'].'-'.$row['healthFacility']]++;
                    } else {
                        $result['summary']['service_branch'][$row['healthFacilityId'].'-'.$row['healthFacility']] = 1;
                    }

                    if(isset($result['summary']['service'][$patientServices[0].'-'.$patientServices[1]][$row['healthFacilityId'].'-'.$row['healthFacility']])) {
                        $result['summary']['service'][$patientServices[0].'-'.$patientServices[1]][$row['healthFacilityId'].'-'.$row['healthFacility']]++;
                    } else {
                        $result['summary']['service'][$patientServices[0].'-'.$patientServices[1]][$row['healthFacilityId'].'-'.$row['healthFacility']] = 1;
                    }
                }
            }

            //PRESCRIPTION
            if($row['preChartType'] != null or $row['preChartType'] != '') {
                if(isset($result['summary']['prescription'][$row['preChartStatus']])) {
                    $result['summary']['prescription'][$row['preChartStatus']]++;
                } else {
                    $result['summary']['prescription'][$row['preChartStatus']] = 1;
                }
            }

            $prescriptionTypes = array(
                array(
                    'status' => $row['preChartStatus'],
                    'order' => $row['preChartStatus'],
                    'type' => 'Prescription',
                ),
                array(
                    'status' => $row['presChartStatus'],
                    'order' => $row['presChartStatus'],
                    'type' => 'Signed Prescription',
                ),
            );

            foreach ($prescriptionTypes as $prescriptionType) {
                if(!empty($prescriptionType['status'])) {
                    if (!isset($result['summary']['prescription'][$row['healthFacility']]['statuses'][$prescriptionType['status']])) {
                        $result['summary']['prescription'][$row['healthFacility']]['statuses'][$prescriptionType['status']] = array(
                            'order' => $prescriptionType['order'],
                            'status' => $prescriptionType['status'],
                            'types' => array()
                        );
                    }
                    if(!isset($result['summary']['prescription'][$row['healthFacility']]['statuses'][$prescriptionType['status']]['types'][$prescriptionType['type']])) {
                        $result['summary']['prescription'][$row['healthFacility']]['statuses'][$prescriptionType['status']]['types'][$prescriptionType['type']] = 0;
                    }

                    $result['summary']['prescription'][$row['healthFacility']]['statuses'][$prescriptionType['status']]['types'][$prescriptionType['type']]++;
                }
            }

            //RESULTS
            $patientFile_split = explode(',',$row['patientFiles']);
            foreach ($patientFile_split as $pf) {
                $patientFile = explode('|', $pf);
                if(isset($patientFile[2])) {
                    if(isset($result['summary']['results'][$patientFile[1].'-'.$patientFile[2]])) {
                        $result['summary']['results'][$patientFile[1].'-'.$patientFile[2]]++;
                    } else {
                        $result['summary']['results'][$patientFile[1].'-'.$patientFile[2]] = 1;
                    }
                }
            }

            //PURCHASE SUMMARY


            $patientBillingInventory_split = explode(',',$row['patientBillingInventory']);
            foreach ($patientBillingInventory_split as $pbi) {
                $patientBillingInventory = explode('|', $pbi);

                if(isset($patientBillingInventory[2])) {
                    if(isset($result['summary']['purchase'][$row['healthFacility']]['statuses'][$patientBillingInventory[2]])) {
                        $result['summary']['purchase'][$row['healthFacility']]['statuses'][$patientBillingInventory[2]]++;
                    } else {
                        $result['summary']['purchase'][$row['healthFacility']]['statuses'][$patientBillingInventory[2]] = 1;
                    }
                }
            }
        }
	    //die(print_r($result['summary']['cycle']));

        foreach ($result['summary']['cycle'] as $key => $cycle) {
            usort($cycle['statuses'], function ($a,$b){
                return $a['order'] > $b['order'];
            });
            $result['summary']['cycle'][$key]['statuses'] = $cycle['statuses'];

            //print_r($cycle);die();

            //            // COMPUTATION OF UNBOOKED STATUS
	        //
	        //            $hsatReferrals = $cycle['hsatReferrals'];
	        //
	        //            $completedHRI = 0;
	        //            $completedHSAT = 0;
	        //            $completedPrescription = 0;
	        //            $completedCPAPSetup = 0;
	        //            $completedInvoice = 0;
	        //
	        //            $totalHsat = 0;
	        //            $totalHRI = 0;
	        //            $totalPrescription = 0;
	        //            $totalCpapSetup = 0;
	        //            $totalInvoice = 0;
	        //            // GETTING ALL VALUE OF STATUS "COMPLETED" PER TYPES
	        //
	        //            foreach($cycle['statuses'] as $status){
	        //                if($status['status'] === 'Completed') {
	        //                    $completedHSAT = isset($status['types']['HSAT']) ? $status['types']['HSAT'] : 0;
	        //                    $completedHRI = isset($status['types']['HSAT Results Interpretation']) ? $status['types']['HSAT Results Interpretation'] : 0;
	        //                    $completedPrescription = isset($status['types']['Prescription']) ? $status['types']['Prescription'] : 0;
	        //                    $completedCPAPSetup = isset($status['types']['CPAP Setup']) ? $status['types']['CPAP Setup'] : 0;
	        //                    $completedInvoice = isset($status['types']['Invoice']) ? $status['types']['Invoice'] : 0;
	        //                    break;
	        //                }
	        //            }
	        //
	        //            foreach($cycle['statuses'] as $status){
	        //                if($status['status'] != 'Unbooked') {
	        //                    $totalHsat += isset($status['types']['HSAT']) ? $status['types']['HSAT'] : 0;
	        //                    $totalHRI += isset($status['types']['HSAT Results Interpretation']) ? $status['types']['HSAT Results Interpretation'] : 0;
	        //                    $totalPrescription += isset($status['types']['Prescription']) ? $status['types']['Prescription'] : 0;
	        //                    $totalCpapSetup += isset($status['types']['CPAP Setup']) ? $status['types']['CPAP Setup'] : 0;
	        //                    $totalInvoice += isset($status['types']['Invoice']) ? $status['types']['Invoice'] : 0;
	        //
	        //                    // $totalHsat_patients = array_merge($totalHsat_patients, (isset($status['types']['HSAT_patients']) ? $status['types']['HSAT_patients'] : []));
	        //                }
	        //            }
	        //
	        //            /*
	        //            // GETTING ALL VALUE OF STATUS "BOOKED" PER TYPES
	        //            foreach($cycle['statuses'] as $status){
	        //                if($status['status'] === 'Booked') {
	        //                    $bookedHRI = isset($status['types']['HSAT Results Interpretation']) ? $status['types']['HSAT Results Interpretation'] : 0;
	        //                    $bookedPrescription = isset($status['types']['Prescription']) ? $status['types']['Prescription'] : 0;
	        //                    $bookedCPAPSetup = isset($status['types']['CPAP Setup']) ? $status['types']['CPAP Setup'] : 0;
	        //                    break;
	        //                }
	        //            }
	        //            */
	        //
	        //
	        //
	        //            $unbookedHSAT = $hsatReferrals - $totalHsat;
	        //            $unbookedHRI = $completedHSAT - $totalHRI;
	        //            $unbookedPrescription = $completedHRI - $totalPrescription;
	        //            $unbookedCPAPSetup = $completedPrescription - $totalCpapSetup;
	        //            $unbookInvoice = $completedCPAPSetup - $totalInvoice;
	        //
	        //            // $unbookedHSAT_patients = array_diff($hsatReferrals_patients, $totalHsat_patients);
	        //            // var_dump($hsatReferrals, $hsatReferrals_patients);die();
	        //            // var_dump($totalHsat, $totalHsat_patients);die();
	        //            // var_dump($unbookedHSAT, $unbookedHSAT_patients);die();
	        //            // var_dump(
	        //            //     [$hsatReferrals, count($hsatReferrals_patients), $this->array_not_unique($hsatReferrals_patients)],
	        //            //     [$totalHsat, count($totalHsat_patients), $this->array_not_unique($totalHsat_patients)],
	        //            //     [$unbookedHSAT, count($unbookedHSAT_patients), $this->array_not_unique($unbookedHSAT_patients)]
	        //            // );die();
	        //            // var_dump(
	        //            //     $hsatReferrals, $this->array_not_unique($hsatReferrals_patients),
	        //            //     $totalHsat, $this->array_not_unique($totalHsat_patients),
	        //            //     $unbookedHSAT, $this->array_not_unique($unbookedHSAT_patients)
	        //            // );die();
	        //
	        //
	        //            // ASSIGNING COMPUTED VALUES ON ITS CORRESPONDING KEY IN "UNBOOKED" ARRAY
	        //            foreach($cycle['statuses'] as $key1 => $status){
	        //                if($status['status'] === 'Unbooked') {
	        //                    if(isset($result['summary']['cycle'][$key]['statuses'][$key1]['types']['HSAT'])){
	        //                        $result['summary']['cycle'][$key]['statuses'][$key1]['types']['HSAT'] = $unbookedHSAT;
	        //                        // $result['unbooked_patients'] = array_merge($result['unbooked_patients'], $unbookedHSAT_patients);
	        //                    }
	        //                    if(isset($result['summary']['cycle'][$key]['statuses'][$key1]['types']['HSAT Results Interpretation'])){
	        //                        $result['summary']['cycle'][$key]['statuses'][$key1]['types']['HSAT Results Interpretation'] = $unbookedHRI;
	        //                    }
	        //                    if(isset($result['summary']['cycle'][$key]['statuses'][$key1]['types']['Prescription'])){
	        //                        $result['summary']['cycle'][$key]['statuses'][$key1]['types']['Prescription'] = $unbookedPrescription;
	        //                    }
	        //                    if(isset($result['summary']['cycle'][$key]['statuses'][$key1]['types']['CPAP Setup'])){
	        //                        $result['summary']['cycle'][$key]['statuses'][$key1]['types']['CPAP Setup'] = $unbookedCPAPSetup;
	        //                    }
	        //                    if(isset($result['summary']['cycle'][$key]['statuses'][$key1]['types']['Invoice'])){
	        //                        $result['summary']['cycle'][$key]['statuses'][$key1]['types']['Invoice'] = $unbookInvoice;
	        //                    }
	        //                    break;
	        //                }
	        //            }
        }


        return $result;
    }

private function array_not_unique($raw_array) {
    $dupes = array();
    natcasesort($raw_array);
    reset($raw_array);

    $old_key   = NULL;
    $old_value = NULL;
    foreach ($raw_array as $key => $value) {
        if ($value === NULL) { continue; }
        if (strcasecmp($old_value, $value) === 0) {
            $dupes[$old_key] = $old_value;
            $dupes[$key]     = $value;
        }
        $old_value = $value;
        $old_key   = $key;
    }
    return $dupes;
}
    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();
    }

}
