<?php

namespace RxConnexionBundle\Repository;

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

        $errors = array();

        $action = $patientChart_form['action'];

        // d = delete
        if($action !== 'd') {
            /*    $patientChartExist = $this->getEntityManager()->getRepository('RxConnexionBundle:ProviderEntity')
                    ->createQueryBuilder('ep')
                    ->where('ep.id != :id')
                    ->andWhere('ep.description = :description')
                    ->setParameters(array(
                        'id' => $patientChart_form['id'],
                        'description' => $patientChart_form['description']
                    ))
                    ->getQuery()->getResult();

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

            /*if(empty($patientChart_form['description']))
                $errors[] = 'Description should not be blank.';*/
            if(empty($patientChart_form['chart_status'])){
                $errors[] = 'Please select status';
            }
        }

        return $errors;
    }

    public function validate_is_done_form($form){
        $errors = array();
        if(empty($form['note'])){
            $errors[] = 'Please enter note';
        }
        if(empty($form['internal_status'])){
            $errors[] = 'Please enter status';
        }
        return $errors;
    }

    public function ajax_list(array $get){

        $columns = array(
            array('hf.`description`', 'hf.`description`', 'health_facility_desc'),
            array('pc.`id`', "pc.`id`"),
            array('ct.`description`', 'ct.`description`', 'chart_type_desc'),
            array('pc.`chart_date`', "DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y')", 'chart_date'),
            array('pr.`full_name`', 'pr.`full_name`', 'provider_fullName'),
            array('cs.`description`', 'cs.`description`', 'chart_status_desc'),
            array('pc.`patient_id`', 'pc.`patient_id`'),
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `patient_chart` pc";
        $joins = "LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`";
        $joins .= " LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`";
        $joins .= " LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`";
        $joins .= " LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`";
        $sqlWhere = "WHERE pc.`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 autocompleteSuggestions($get) {

        $andWhere = '';
        $stmtParams = array('q' => '%' . $get['query'] . '%');

        if(isset($get['patient_id'])) {
            $andWhere .= ' AND pc.`patient_id` = :pt_id';
            $stmtParams['pt_id'] = $get['patient_id'];
        }

        if(isset($get['patient_ids']) && !empty($get['patient_ids'])) {
            $patientIds = explode('|',$get['patient_ids']);
            if(count($patientIds)){
                $ors = "";
                foreach ($patientIds as $key => $patientId) {
                    $ors .= (empty($ors) ? '' : ' OR ').'pc.`patient_id` = :pt_id'.$key;
                    $stmtParams['pt_id'.$key] = $patientId;
                }
                $andWhere .= " AND ($ors)";
            }
        }

        $field = "CONCAT_WS(' - ', pc.`id`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), ct.`description`)";
        $query = $this->getEntityManager()->getConnection()->prepare("
            SELECT
                pc.`id`,
                $field AS data,
                $field AS value,
                pc.`chart_type_id` AS chartType_id
            FROM `patient_chart` pc
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            WHERE $field LIKE :q
            AND pc.`is_deleted` = 0
            AND ct.`has_billing` = 1
            $andWhere
            ORDER BY $field DESC
            LIMIT 0,10
        ");
        $query->execute($stmtParams);

        return $query->fetchAll();
    }

    public function autocompleteSuggestionsForBilling($get) {

        $andWhere = '';
        $stmtParams = array('q' => '%' . $get['query'] . '%');

        if(isset($get['patient_id'])) {
            $andWhere .= ' AND pc.`patient_id` = :pt_id';
            $stmtParams['pt_id'] = $get['patient_id'];
        }

        $field = "CONCAT_WS(' - ', pc.`id`, DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y'), ct.`description`)";
        $query = $this->getEntityManager()->getConnection()->prepare("
            SELECT
                pc.`id`,
                $field AS data,
                $field AS value,
                pc.`chart_type_id` AS chartType_id
            FROM `patient_chart` pc
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            WHERE $field LIKE :q
            AND pc.`is_deleted` = 0
            AND ct.`has_billing` = 1
            AND (pc.`billing_status` IS NULL OR pc.`billing_status` != 'Paid') 
            $andWhere
            ORDER BY $field DESC
            LIMIT 0,10
        ");
        $query->execute($stmtParams);

        return $query->fetchAll();
    }

    // REPORTS
    public function reportEncounter($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 = "
            SELECT
                pc.`id` AS encounterId,
                p.`full_name` AS clinician,
                pat.`id` AS patientId,
                pat.`full_name` AS patient,
                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,
                CONCAT('[ ' , DATE_FORMAT(pnote.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', pnote.`created_by`, ' ] : ', pnote.`note`) AS note
            FROM `patient_chart` pc
            LEFT JOIN `provider` p ON p.`id` = pc.`provider_id`
            LEFT JOIN `patient` pat ON pat.`id` = pc.`patient_id`
            LEFT JOIN `chart_type` ct ON ct.`id` = pc.`chart_type_id`
            LEFT JOIN `chart_status` cs ON cs.`id` = pc.`chart_status_id`
            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 `patient_note` pnote ON pnote.`id` = (
              SELECT pnote1.`id`
              FROM `patient_note` pnote1
              WHERE pnote1.`is_deleted` = 0
              AND pnote1.`patient_id` = pc.`patient_id`
              ORDER BY pnote1.`created_at` DESC
              LIMIT 1
            )
            $sqlWhere
            AND pc.`patient_id` IS NOT NULL
            ORDER BY pc.`chart_date` DESC 
        ";
        $query = $this->getEntityManager()->getConnection()->prepare($sql);

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    //encounter type statuses
    public function findEncounterTypeStatus() {

        $sql = "
            SELECT 
              cs.`id`,
              cs.`description` status
            FROM `chart_status` cs 
            WHERE cs.`is_deleted` = 0
            GROUP BY cs.`description`
        ";

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

        $query->execute();

        $result = $query->fetchAll();

        return $result;
    }

    public function getPatientLatestChart($patientId, $chartId) {
        $conn = $this->getEntityManager()->getConnection();
        $stmtParams = array(
            'patientId' => $patientId,
            'chartId' => $chartId
        );
        $sql = $conn->prepare(
            "
           SELECT
                pc.`id` AS patientChartId,
                cs.`description` AS chartStatus,
                ct.`description` AS chartType,
                DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') AS chartDate,
                hf.`description` AS healthFacility,
                p.`full_name` AS provider
            FROM `patient_chart` pc
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id` 
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id` 
            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id` 
            LEFT JOIN `provider` p ON pc.`provider_id` = p.`id`
            LEFT JOIN `schedule` s ON pc.`schedule_id` = s.`id`
            WHERE pc.`patient_id` = :patientId
            AND pc.`id` != :chartId
            AND pc.`is_deleted` = 0
            ORDER BY pc.`id` DESC, pc.`chart_date` DESC
            
        "

        );

        $sql->execute($stmtParams);
        $result = $sql->fetchAll();
        if(isset($result[0])){
            return $result[0];
        }


    }

    public function  productivityReport($params) {

        $stmtParams = array();
        $stmtParamsBranch = array();
        $sqlWhere = "WHERE (pc.`is_deleted` = 0 OR pc.`is_deleted` IS NOT NULL) ";
        $sqlWhereBranch = "WHERE (hf.`is_deleted` = 0 OR hf.`is_deleted` IS NOT NULL)";
        $sqlWhereBranchClinician = "";

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

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

        if(isset($params['clinician']) && !empty($params['clinician']) && 'NULL' !== strtoupper($params['clinician'])){
            $clinicians = explode(',' , $params['clinician']);
            if(count($clinicians) > 0){
                $clinician_ors = '';
                foreach($clinicians as $key => $clinicianId){
                    $clinician_ors .= (empty($clinician_ors) ? '' : ' OR ') . "p.`id` = :clinician_id_$key";
                    $stmtParams["clinician_id_$key"] = $clinicianId;

                    $clinician_ors .= (empty($clinician_ors) ? '' : ' OR ') . "p.`id` = :clinician_id_$key";
                    $stmtParamsBranch["clinician_id_$key"] = $clinicianId;
                }
                $sqlWhere .= " AND ($clinician_ors) ";
                $sqlWhereBranchClinician .= " AND ($clinician_ors) ";
            }
        }


        $sql_chart = "
            SELECT
                p.`id` clinician_id,
                ct.`id` encounter_type_id,
                hf.`id` health_facility_id,
                p.`full_name` clinician,
                hf.`description` health_facility,
                pc.`chart_date`,
                ct.`description` encounterType,
                cs.`description` encounterStatus,
                COUNT(DISTINCT pc.`id`) total_count
            FROM `patient_chart` pc
            LEFT JOIN `provider` p ON p.`id` = pc.`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`
            $sqlWhere
            AND cs.`description` = 'Completed'
            AND p.`is_active` = 1
            AND p.`full_name` IS NOT NULL
            AND p.`full_name` != ''
            AND pc.`is_deleted` = 0
            GROUP BY pc.health_facility_id, pc.`provider_id`, pc.`chart_type_id`
            ORDER BY ct.`rpt_prod_ordinal` IS NULL, ct.`rpt_prod_ordinal` ASC
        ";

        $sql_hf = "
            SELECT 
                hf.`id`,
                phf.`details`,
                hf.`description`
            FROM `health_facility` hf
            LEFT JOIN (
                    SELECT GROUP_CONCAT(CONCAT_WS('~', p.`id`,p.`full_name`) SEPARATOR '|') as details,
                            phf.`health_facility_id`
                    FROM `provider_health_facility` phf
                    LEFT JOIN `provider` p ON phf.`provider_id` = p.`id`
                    WHERE phf.`provider_id` IS NOT NULL
                    AND p.`full_name` IS NOT NULL
                    AND p.`full_name` != ''
                    AND p.`is_active` = 1
                    $sqlWhereBranchClinician
                    AND (phf.`is_deleted` = 0 OR phf.`is_deleted` IS NULL)
                    GROUP BY phf.`health_facility_id`
            ) phf ON phf.`health_facility_id` = hf.`id`
            $sqlWhereBranch
        ";

        $query1 = $this->getEntityManager()->getConnection()->prepare($sql_chart);
        $query2 = $this->getEntityManager()->getConnection()->prepare($sql_hf);
        $query1->execute($stmtParams);
        $query2->execute($stmtParamsBranch);

        $result1 = $query1->fetchAll();
        $result2 = $query2->fetchAll();

        $healthFacilities = array();
        foreach ($result2 as $result) {

            $providers = array();
            $chartTypes = array();

            foreach (explode('|', $result['details']) as $row) {
                $provider = explode('~', $row);
                if(isset($provider[1])){
                    $providers[] = array(
                        'id' => $provider[0],
                        'name' => $provider[1]
                    );
                }
            }

            foreach ($result1 as $res) {
                if($result['id'] == $res['health_facility_id']) {
                    if(isset($chartTypes[$res['encounter_type_id']])) {
                        $chartTypes[$res['encounter_type_id']]['providers'][] = array(
                            'id' => $res['clinician_id'],
                            'count' => $res['total_count'],
                        );
                    } else {
                        $chartTypes[$res['encounter_type_id']] = array(
                            'id' => $res['encounter_type_id'],
                            'description' => $res['encounterType'],
                            'providers' => array(
                                array(
                                    'id' => $res['clinician_id'],
                                    'count' => $res['total_count'],
                                )
                            )
                        );
                    }
                }
            }

            $healthFacilities[] = array(
                'id' => $result['id'],
                'description' => $result['description'],
                'providers' => $providers,
                'chartTypes' => $chartTypes
            );
        }

        unset($result1);
        unset($result2);

        return $healthFacilities;
    }

    public function productivityClinicianReport($params) {

        $stmtParams = array();
        $stmtParamsBranch = array();
        $sqlWhere = "WHERE (pc.`is_deleted` = 0 OR pc.`is_deleted` IS NOT NULL) ";
        $sqlWhereBranch = "WHERE (hf.`is_deleted` = 0 OR hf.`is_deleted` IS NOT NULL)";
        $sqlWhereBranchClinician = "";

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

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

                    $hf_ors1 .= (empty($hf_ors1) ? '' : ' OR ') . "hf.`id` = :healthFacility_id_$key";
                    $stmtParamsBranch["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere.= " AND ($hf_ors) ";
                $sqlWhereBranch .= " AND ($hf_ors1) ";
            }
        }

        if(isset($params['clinician']) && !empty($params['clinician']) && 'NULL' !== strtoupper($params['clinician'])){
            $clinicians = explode(',' , $params['clinician']);
            if(count($clinicians) > 0){
                $clinician_ors = '';
                $clinician_ors1 = '';
                foreach($clinicians as $key => $clinicianId){
                    $clinician_ors .= (empty($clinician_ors) ? '' : ' OR ') . "au.`provider_id` = :clinician_id_$key";
                    $stmtParams["clinician_id_$key"] = $clinicianId;

                    $clinician_ors1 .= (empty($clinician_ors1) ? '' : ' OR ') . "p.`id` = :clinician_id_$key";
                    $stmtParamsBranch["clinician_id_$key"] = $clinicianId;
                }
                $sqlWhere .= " AND ($clinician_ors) ";
                $sqlWhereBranchClinician .= " AND ($clinician_ors) ";
            }
        }


        $sql_chart = "
            SELECT
                au.`provider_id` clinician_id,
                ct.`id` encounter_type_id,
                hf.`id` health_facility_id,
                p.`full_name` clinician,
                hf.`description` health_facility,
                pc.`chart_date`,
                ct.`description` encounterType,
                cs.`description` encounterStatus,
                COUNT(DISTINCT pc.`id`) total_count
            FROM `patient_chart` pc
            LEFT JOIN `account_user` au ON au.`provider_id` = pc.`provider_id`
            LEFT JOIN `provider` p ON p.`id` = pc.`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`
            $sqlWhere
            AND cs.`description` = 'Completed'
            AND au.`is_active` = 1
            AND au.`full_name` IS NOT NULL
            AND au.`full_name` != ''
            AND pc.`is_deleted` = 0
            GROUP BY pc.health_facility_id, pc.`provider_id`, pc.`chart_type_id`
            ORDER BY ct.`rpt_prod_ordinal` IS NULL, ct.`rpt_prod_ordinal` ASC
        ";

        $sql_hf = "
            SELECT 
                hf.`id`,
                phf.`details`,
                hf.`description`
            FROM `health_facility` hf
            LEFT JOIN (
                    SELECT GROUP_CONCAT(CONCAT_WS('~', au.`provider_id`,au.`full_name`) SEPARATOR '|') as details,
                            phf.`health_facility_id`
                    FROM `provider_health_facility` phf
                    LEFT JOIN `account_user` au ON au.`provider_id` = phf.`provider_id`
                    WHERE phf.`provider_id` IS NOT NULL
                    AND au.`full_name` IS NOT NULL
                    AND au.`full_name` != ''
                    AND au.`is_active` = 1
                    $sqlWhereBranchClinician
                    AND (phf.`is_deleted` = 0 OR phf.`is_deleted` IS NULL)
                    GROUP BY phf.`health_facility_id`
            ) phf ON phf.`health_facility_id` = hf.`id`
            $sqlWhereBranch
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
        ";

//        $sql_invoice = "SELECT
//						  pch.`health_facility_id`,
//						  pch.`provider_id`,
//						  pch_prov.`full_name`,
//                          SUM(IF(pb.`billing_date` IS NOT NULL, 1, 0)) AS invoicesCount
//                        FROM `patient` p
//                        LEFT JOIN `patient_chart` pch ON pch.`id` = (
//                          SELECT
//                            pct1.`id`
//                          FROM `patient_chart` pct1
//                          LEFT JOIN `chart_type` ctt1 ON pct1.`chart_type_id` = ctt1.`id`
//                          LEFT JOIN `chart_status` cs1 ON cs1.`chart_type_id` = ctt1.`id`
//                          LEFT JOIN `chart_status` cs_ordinal1 ON cs_ordinal1.`id` = pct1.`chart_status_id`
//                          WHERE ctt1.`description` = 'HSAT'
//                          AND cs1.`description` IN('Booked', 'Completed')
//                          AND pct1.`patient_id` = p.`id`
//                          AND pct1.`is_deleted` = 0
//                          ORDER BY pct1.`chart_date` DESC, cs_ordinal1.`ordinal` DESC
//                          LIMIT 1
//                        )
//                        LEFT JOIN `health_facility` hf ON pch.`health_facility_id` = hf.`id`
//                        LEFT JOIN `company` co ON hf.`company_id` = co.`id`
//                        LEFT JOIN `provider` prov ON p.`provider_id` = prov.`id`
//                        LEFT JOIN `provider` pch_prov ON pch.`provider_id` = pch_prov.`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` DESC
//                          LIMIT 1
//                        )
//                        LEFT JOIN `health_facility` hfpb ON pb.`health_facility_id` = hfpb.`id`
//                        LEFT JOIN `chart_type` cth ON pch.`chart_type_id` = cth.`id`
//                        LEFT JOIN `chart_status` csh ON pch.`chart_status_id` = csh.`id`
//                        WHERE p.`is_deleted` = 0
//                         AND p.`referral_date` BETWEEN DATE_FORMAT(:dateFrom, '%Y-%m-%d') AND DATE_FORMAT(:dateTo, '%Y-%m-%d')
//                        AND p.`full_name` IS NOT NULL
//                        AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
//                        AND hf.`description` IS NOT NULL
//                        AND p.`referral_date` IS NOT NULL
//                        AND p.`referral_date` != '0000-00-00 00:00:00'
//                        GROUP BY pch.`health_facility_id`, pch.`provider_id`";

        $conn = $this->getEntityManager()->getConnection();

        $query1 = $conn->prepare($sql_chart);
        $query2 = $conn->prepare($sql_hf);
//        $query3 = $conn->prepare($sql_invoice);

        $query1->execute($stmtParams);
        $query2->execute($stmtParamsBranch);
//        $query3->execute();

        $result1 = $query1->fetchAll();
        $result2 = $query2->fetchAll();
//        $result3 = $query3->fetchAll();

        $healthFacilities = array();
        foreach ($result2 as $result) {

            $providers = array();
            $chartTypes = array();

            foreach (explode('|', $result['details']) as $row) {
                $provider = explode('~', $row);
                if(isset($provider[1])){
                    $providers[] = array(
                        'id' => $provider[0],
                        'name' => $provider[1]
                    );
                }
            }

            foreach ($result1 as $res) {
                if($result['id'] == $res['health_facility_id']) {
                    if(isset($chartTypes[$res['encounter_type_id']])) {
                        $chartTypes[$res['encounter_type_id']]['providers'][] = array(
                            'id' => $res['clinician_id'],
                            'count' => $res['total_count'],
                        );
                    } else {
                        $chartTypes[$res['encounter_type_id']] = array(
                            'id' => $res['encounter_type_id'],
                            'description' => $res['encounterType'],
                            'providers' => array(
                                array(
                                    'id' => $res['clinician_id'],
                                    'count' => $res['total_count'],
                                )
                            )
                        );
                    }
                }
            }

            $healthFacilities[] = array(
                'id' => $result['id'],
                'description' => $result['description'],
                'providers' => $providers,
                'chartTypes' => $chartTypes,
            );
        }

        unset($result1);
        unset($result2);
//        unset($result3);


        return $healthFacilities;

    }

    public function productivityClinicianSummary($params) {

        $stmtParams = array();
        $stmtParamsBranch = array();
        $sqlWhere = "WHERE (pc.`is_deleted` = 0 OR pc.`is_deleted` IS NOT NULL) ";
        $sqlWhereBranch = "";
        $sqlWhereBranchClinician = "";

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

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

        if(isset($params['clinician']) && !empty($params['clinician']) && 'NULL' !== strtoupper($params['clinician'])){
            $clinicians = explode(',' , $params['clinician']);
            if(count($clinicians) > 0){
                $clinician_ors = '';
                $clinician_ors1 = '';
                foreach($clinicians as $key => $clinicianId){
                    $clinician_ors .= (empty($clinician_ors) ? '' : ' OR ') . "p.`id` = :clinician_id_$key";
                    $stmtParams["clinician_id_$key"] = $clinicianId;

                    $clinician_ors1 .= (empty($clinician_ors1) ? '' : ' OR ') . "pr.`id` = :clinician_id_$key";
                    $stmtParamsBranch["clinician_id_$key"] = $clinicianId;
                }
                $sqlWhere .= " AND ($clinician_ors) ";
                $sqlWhereBranchClinician .= " AND ($clinician_ors1) ";
            }
        }

        $sql_clinician = "
             SELECT
                pc.`provider_id`,
 				pc.`chart_type_id`,
 				ct.`description` AS chartType,
                p.`full_name` AS clinician,
                COUNT(DISTINCT pc.`id`) total_count
            FROM `patient_chart` pc
            LEFT JOIN `account_user` au ON au.`provider_id` = pc.`provider_id`
            LEFT JOIN `provider` p ON p.`id` = pc.`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`
            $sqlWhere
            $sqlWhereBranch
            AND cs.`description` = 'Completed'
            AND au.`is_active` = 1
            AND au.`full_name` IS NOT NULL
            AND au.`full_name` != ''
            AND pc.`is_deleted` = 0
            GROUP BY pc.`chart_type_id`, pc.`provider_id`
            ORDER BY ct.`rpt_prod_ordinal` IS NULL, ct.`rpt_prod_ordinal` ASC
        ";

        $sql_provider = "
            SELECT 
              pr.`id` AS provider_id, 
              pr.`full_name` AS provider
            FROM `provider` pr
            WHERE pr.`is_deleted` = 0
            $sqlWhereBranchClinician
        ";

        $conn = $this->getEntityManager()->getConnection();
        $query1 = $conn->prepare($sql_provider);
        $query2 = $conn->prepare($sql_clinician);
        $query1->execute($stmtParamsBranch);
        $query2->execute($stmtParams);

        $result1 = $query1->fetchAll();
        $result2 = $query2->fetchAll();

        $patientChartCount = array();

        foreach ($result1 as $key1 => $provider) {
            $chartTypeCount = array();

            foreach ($result2 as $key2 => $patientCharts) {
                if($provider['provider_id'] == $patientCharts['provider_id']) {
                    $chartTypeCount[] = array(
                        'clinician' => $patientCharts['clinician'],
                        'patient_chart_count' => array(
                            'chartType' => $patientCharts['chartType'],
                            'chart_status_count' => $patientCharts['total_count'],
                        )
                    );
                }
            }

            if (count($chartTypeCount)) {
                $patientChartCount[] = array(
                    'provider' => $provider['provider'],
                    'chart_types' => $chartTypeCount,

                );
            }

        }

        return $patientChartCount;
    }

    public function productivityClinicianSummaryHf($params) {

        $stmtParams = array();
        $stmtParamsBranch = array();
        $sqlWhere = "WHERE (pc.`is_deleted` = 0 OR pc.`is_deleted` IS NOT NULL) ";
        $sqlWhereBranch = "";
        $sqlWhereBranchClinician = "";

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

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

        if(isset($params['clinician']) && !empty($params['clinician']) && 'NULL' !== strtoupper($params['clinician'])){
            $clinicians = explode(',' , $params['clinician']);
            if(count($clinicians) > 0){
                $clinician_ors = '';
                foreach($clinicians as $key => $clinicianId){
                    $clinician_ors .= (empty($clinician_ors) ? '' : ' OR ') . "p.`id` = :clinician_id_$key";
                    $stmtParams["clinician_id_$key"] = $clinicianId;
                }
                $sqlWhere .= " AND ($clinician_ors) ";
            }
        }

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

        $sql_chart = "
             SELECT
                pc.`chart_type_id`,
                pc.`health_facility_id`,
                ct.`description` AS chartType,
				hf.`description` AS healthFacility,
                COUNT(DISTINCT pc.`id`) AS total_count
            FROM `patient_chart` pc
            LEFT JOIN `account_user` au ON au.`provider_id` = pc.`provider_id`
            LEFT JOIN `provider` p ON p.`id` = pc.`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`
            $sqlWhere
            AND cs.`description` = 'Completed'
            AND au.`is_active` = 1
            AND au.`full_name` IS NOT NULL
            AND pc.`health_facility_id` IS NOT NULL
            AND au.`full_name` != ''
            AND pc.`is_deleted` = 0
            GROUP BY pc.`health_facility_id`, pc.`chart_type_id`
            ORDER BY ct.`rpt_prod_ordinal` IS NULL, ct.`rpt_prod_ordinal` ASC
        ";

        $sql_hf = "
            SELECT 
               hf.`id` AS healthFacilityId,
               hf.`description`
            FROM `health_facility` hf
            WHERE hf.`is_deleted` = 0
            AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test', 'Level 1')
            $sqlWhereBranch
        ";

        $conn = $this->getEntityManager()->getConnection();
        $query1 = $conn->prepare($sql_chart);
        $query2 = $conn->prepare($sql_hf);
        $query1->execute($stmtParams);
        $query2->execute($stmtParamsBranch);

        $result1 = $query1->fetchAll();
        $result2 = $query2->fetchAll();

        $hfClinicianProductivity = array();
        $healthFacilities = array();
        $chartTypes = array();

        foreach ($result2 as $result) {
            $healthFacilities[] = array(
                'id' => $result['healthFacilityId'],
                'name' => $result['description']
            );
        }

        foreach ($result1 as $res) {
            if(isset($chartTypes[$res['chart_type_id']])) {
                $chartTypes[$res['chart_type_id']]['healthFacility'][] = array(
                    'healthFacility_id' => $res['health_facility_id'],
                    'totalCount' => $res['total_count'],
                );

            } else {
                $chartTypes[$res['chart_type_id']] = array(
                    'healthFacility_id' => $res['chart_type_id'],
                    'description' => $res['chartType'],
                    'healthFacility' => array(
                        array(
                            'healthFacility_id' => $res['health_facility_id'],
                            'totalCount' => $res['total_count'],
                        )
                    )
                );
            }
        }

        $hfClinicianProductivity[] = array(
            'healthFacility' => $healthFacilities,
            'chartTypes' => $chartTypes
        );

        unset($result1);
        unset($result2);

        return $hfClinicianProductivity;
    }

    public function productivityBillingReport($params) {

        $stmtParams = array();
        $sqlWhere = "WHERE (pc.`is_deleted` = 0 OR pc.`is_deleted` IS NOT NULL) ";

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

        $sql_chart = "
            SELECT
                aub.`id` accountUserBilling_id,
                ct.`id` encounter_type_id,
                aub.`full_name` accountUserBilling_fullname,
                pc.`chart_date`,
                ct.`description` encounterType,
                cs.`description` encounterStatus,
                COUNT(DISTINCT pc.`id`) total_count
            FROM `patient_chart` pc
            LEFT JOIN `account_user_billing` aub ON pc.`account_user_billing_id` = aub.`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`
            $sqlWhere
            AND aub.`full_name` IS NOT NULL
            AND aub.`full_name` != ''
            AND pc.`is_deleted` = 0
            GROUP BY pc.`account_user_billing_id`, ct.`id`
        ";

        $sql_auBilling = "
            SELECT 
               aub.`id`,
               aub.`full_name`
            FROM `account_user_billing` aub
            LEFT JOIN `account_user` au ON au.`account_user_billing_id` = aub.`id`
            WHERE (aub.`is_deleted` = 0 OR aub.`is_deleted` IS NOT NULL)
        ";

        $query1 = $this->getEntityManager()->getConnection()->prepare($sql_chart);
        $query2 = $this->getEntityManager()->getConnection()->prepare($sql_auBilling);
        $query1->execute($stmtParams);
        $query2->execute();

        $result1 = $query1->fetchAll();
        $result2 = $query2->fetchAll();

        $arProductivity = array();
        $auBilling = array();
        $chartTypes = array();

        foreach ($result2 as $result) {
            $auBilling[] = array(
                'id' => $result['id'],
                'name' => $result['full_name']
            );
        }

        foreach ($result1 as $res) {
            if(isset($chartTypes[$res['encounter_type_id']])) {
                $chartTypes[$res['encounter_type_id']]['auBilling'][] = array(
                    'auBilling_id' => $res['accountUserBilling_id'],
                    'totalCount' => $res['total_count'],
                );
            } else {
                $chartTypes[$res['encounter_type_id']] = array(
                    'auBilling_id' => $res['encounter_type_id'],
                    'description' => $res['encounterType'],
                    'auBilling' => array(
                        array(
                            'auBilling_id' => $res['accountUserBilling_id'],
                            'totalCount' => $res['total_count'],
                        )
                    )
                );
            }
        }

        $arProductivity[] = array(
            'auBilling' => $auBilling,
            'chartTypes' => $chartTypes
        );

        unset($result1);
        unset($result2);

        return $arProductivity;
    }

    public function productivityCbtEncounter($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 ") . "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'])));
        }

        $sql_encounters = "
                SELECT  
                    ct.`description` AS chartType,
                    COUNT(ct.`id`) AS processCount
                FROM `patient` p
                LEFT JOIN `patient_chart` pc ON pc.`id` = (
                    SELECT pc1.`id`
                    FROM `patient_chart` pc1
                    WHERE (pc1.`is_deleted` = 0 OR pc1.`is_deleted` IS NOT NULL)
                    AND pc1.`patient_id` = p.`id`
                    ORDER BY pc1.`chart_date` DESC 
                    LIMIT 1
                )
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                $sqlWhere
                AND ct.`description` IS NOT NULL
                AND ct.`chart_type_category` = 'CBT'
                GROUP BY ct.`id`
                ORDER BY FIELD(ct.`description`, 
                'Intro Phone Call', 
                'Patient Review', 
                'Patient Data Transfer', 
                'Phone Call - MA (CBT)', 
                'Phone Call - AR (CBT)', 
                'Care Call', 
                'Prescription', 
                'Payment', 
                'Appointment Notice')
        ";

        $sql_endorsed = "
                SELECT  
                    COUNT(pc.`is_endorsed`) AS endorsedCount
                FROM `patient` p
                LEFT JOIN `patient_chart` pc ON pc.`id` = (
                    SELECT pc1.`id`
                    FROM `patient_chart` pc1
                    WHERE (pc1.`is_deleted` = 0 OR pc1.`is_deleted` IS NOT NULL)
                    AND pc1.`patient_id` = p.`id`
                    ORDER BY pc1.`chart_date` DESC 
                    LIMIT 1
                )
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                $sqlWhere
        ";

        $query1 = $this->getEntityManager()->getConnection()->prepare($sql_encounters);
        $query2 = $this->getEntityManager()->getConnection()->prepare($sql_endorsed);

        $query1->execute($stmtParams);
        $query2->execute($stmtParams);

        $result['encounterProcessed'] = $query1->fetchAll();
        $result['endorsed'] = $query2->fetchAll();

        return $result;
    }

    public function productivityCbtScheduling($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 ") . "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'])));
        }

        $sql = "
                SELECT  
                    ct.`description` AS chartType,
                    SUM(IF(cs.`description` = 'Booked', 1, 0)) AS totalBookedCount,
                    COUNT(ct.`id`) AS totalCount
                FROM `patient` p
                LEFT JOIN `patient_chart` pc ON pc.`id` = (
                    SELECT pc1.`id`
                    FROM `patient_chart` pc1
                    WHERE (pc1.`is_deleted` = 0 OR pc1.`is_deleted` IS NOT NULL)
                    AND pc1.`patient_id` = p.`id`
                    ORDER BY pc1.`chart_date` DESC 
                    LIMIT 1
                )
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                $sqlWhere
                AND ct.`description` IN(
                'HSAT', 
                'HSAT Curbside', 
                'HSAT Curbside (Repeat)', 
                'HSAT (Mobile)', 
                'HSAT Mobile (Repeat)', 
                'HSAT Repeat (No Charge)', 
                'HSAT Results Interpretation', 
                'HSAT Results Interpretation - Telehealth', 
                'CPAP Eval', 
                'CPAP End Eval', 
                'CPAP End Eval (Mobile)', 
                'CPAP Setup', 
                'CPAP Set Up (Mobile)', 
                'CPAP Setup - Telehealth', 
                'Care Call', 
                'Oximetry', 
                're-Titration', 
                'Assessment', 
                '3 Month Follow-up', 
                '6 Month Follow-up', 
                'Done - No further Contact Req.', 
                'Consultation - Check Machine', 
                'Consultation - Mask', 
                'Consultation - Other (make comment)'
                )
                AND ct.`description` IS NOT NULL
                GROUP BY ct.`id`
                ORDER BY FIELD(ct.`description`, 'HSAT', 'HSAT (Mobile)', 'HSAT Repeat (No Charge)', 'HSAT Results Interpretation', 'CPAP Eval', 'CPAP End Eval', 'CPAP End Eval (Mobile)', 'CPAP Setup', 'CPAP Set Up (Mobile)', 'Care Call', 'Oximetry', 're-Titration', 'Assessment', '3 Month Follow-up', '6 Month Follow-up', 'Done - No further Contact Req.', 'Consultation - Check Machine', 'Consultation - Mask', 'Consultation - Other (make comment)')
        ";

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

        $result = $query->fetchAll();

        return $result;
    }

    public function keyPerformanceIndicatorReport($params) {

        $stmtParams = array();
        $stmtParamsStaff = array();
        $sqlWhere = "";
        $sqlWhereStaff = "";

        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.`updated_at` 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['account_user']) && $params['account_user'] !== 'null' && $params['account_user'] !== 'all' && !empty($params['account_user']) && is_numeric($params['account_user'])) {
            $sqlWhereStaff .= (empty($sqlWhereStaff) ? "WHERE " : " AND ") ."temp_pc.`accountUserId` = :account_user";
            $stmtParamsStaff['account_user'] = $params['account_user'];
        }

        $sql_chart = "
            SELECT
				au.`id` accountUserId,
				au.`full_name` fullName,
                ct.`id` encounterTypeId,
                ct.`description` encounterType,
                ins.`id` internalStatusId,
                ins.`description` internalStatus,
				COUNT(DISTINCT ins.`id`) internalStatusCount              
            FROM `patient_chart` pc
            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`
            LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN `account_user` au ON au.username = pc.`updated_by`
            LEFT JOIN (
                  SELECT pct.`id`, pct.`patient_id`, pct.`chart_type_id`, MAX(pct.`chart_date`) AS chart_date
                  FROM `patient_chart` pct
                  WHERE pct.`is_deleted` = 0
                  GROUP BY pct.`patient_id`, pct.`chart_type_id`
            ) pct ON pc.`patient_id` = pct.`patient_id` AND pc.`chart_type_id` = pct.`chart_type_id` 
            LEFT JOIN (
                  SELECT pcn.`id`, pcn.`patient_chart_id`, MAX(pcn.`created_at`) AS created_at
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pcn.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`
             ) pcnt ON pcnt.`patient_chart_id` = pc.`id`
             LEFT JOIN (
                  SELECT pcn.`note`, pcn.`patient_chart_id`, pcn.`created_at`, pcn.`created_by`
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pcn.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`, pcn.`created_at`
             ) pcn ON pcn.`patient_chart_id` = pc.`id` AND pcnt.`created_at` = pcn.`created_at`
            $sqlWhere
            AND pc.`chart_date` != '0000-00-00 00:00:00'
            AND (pc.`is_deleted` = '0' OR pc.`is_deleted` IS NULL)
            AND cs.`description` IN ('No Show', 'Cancelled')
            AND p.`is_active` = 1
            AND pc.`is_deleted` = 0
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
            AND (pct.`id` IS NULL OR pc.`chart_date` >= pct.`chart_date`)
            AND au.`id` IS NOT NULL AND ins.`id` IS NOT NULL
            GROUP BY ct.`id`, ins.`id`, au.`id`
            ORDER BY pc.`chart_date` DESC, hf.`description`
        ";

        $sql_au = "
            SELECT 
				temp_pc.accountUserId id,
				temp_pc.fullName,
                GROUP_CONCAT(CONCAT_WS('~', ins.`id`,ins.`description`) SEPARATOR '|') as details
            FROM `internal_status` ins
            LEFT JOIN (
					SELECT 
					  au.`id` accountUserId,
	                  au.`full_name` fullName,
	                  pc.`internal_status_id` internalStatusId 
					FROM `patient_chart` pc
					LEFT JOIN `account_user` au ON au.`username` = pc.`updated_by`
					GROUP BY au.`id` ,pc.`internal_status_id`
				) temp_pc ON temp_pc.`internalStatusId` = ins.`id`
			$sqlWhereStaff
			AND temp_pc.`accountUserId` IS NOT NULL AND ins.`id` IS NOT NULL
			GROUP BY temp_pc.`accountUserId`
        ";


        $query1 = $this->getEntityManager()->getConnection()->prepare($sql_chart);
        $query2 = $this->getEntityManager()->getConnection()->prepare($sql_au);

        $query1->execute($stmtParams);
        $query2->execute($stmtParamsStaff);

        $result1 = $query1->fetchAll();
        $result2 = $query2->fetchAll();

        $forRebooking = array();

        foreach ($result2 as $result) {

            $internalStatuses = array();
            $chartTypes = array();

            foreach (explode('|', $result['details']) as $row) {
                $internalStatus = explode('~', $row);
                $internalStatuses[] = array(
                    'id' => $internalStatus[0],
                    'description' => $internalStatus[1]
                );
            }

            foreach ($result1 as $res) {
                if($result['id'] == $res['accountUserId']) {
                    if(isset($chartTypes[$res['encounterTypeId']])) {
                        $chartTypes[$res['encounterTypeId']]['internalStatus'][] = array(
                            'id' => $res['internalStatusId'],
                            'count' => $res['internalStatusCount'],
                        );
                    } else {
                        $chartTypes[$res['encounterTypeId']] = array(
                            'description' => $res['encounterType'],
                            'internalStatuses' => array(
                                array(
                                    'id' => $res['internalStatusId'],
                                    'count' => $res['internalStatusCount'],
                                )
                            )
                        );
                    }
                }
            }

            $forRebooking[] = array(
                'id' => $result['id'],
                'fullName' => $result['fullName'],
                'internalStatuses' => $internalStatuses,
                'chartTypes' => $chartTypes
            );
        }

        unset($result1);
        unset($result2);

        return $forRebooking;
    }

    public function getPatientLatestChartWithVitals($patientId) {
        $conn = $this->getEntityManager()->getConnection();
        $stmtParams = array(
            'patientId' => $patientId
        );
        $sql = $conn->prepare(
            "
           SELECT
                pc.`id` AS patientChartId,
                pc.`height`,
                pc.`weight`,
                pc.`blood_pressure_first` AS bloodPressureFirst,
                pc.`blood_pressure_second` AS bloodPressureSecond,
                pc.`pulse`,
                pc.`temperature`,
                pc.`respiratory_rate` AS respiratoryRate,
                pc.`bmi`,
                pc.`nose`,
                pc.`stop_bang`,
                pc.`neck_size`,
                pc.`smoker`,
                pc.`pre_ess`,
                pc.`otwo_saturation`,
                pc.`drinker`,
                pc.`post_ess`,
                DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') AS chartDate,
                cs.`description` AS chartStatus,
                ct.`description` AS chartType,
                hf.`description` AS healthFacility,
                p.`full_name` AS provider,
                pat.`endorsed_by` AS endorsed
            FROM `patient_chart` pc
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id` 
            LEFT JOIN `patient` pat ON pc.`patient_id` = pat.`id` 
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id` 
            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id` 
            LEFT JOIN `provider` p ON pc.`provider_id` = p.`id`
            LEFT JOIN `schedule` s ON pc.`schedule_id` = s.`id`
            WHERE pc.`patient_id` = :patientId
            -- AND ct.`has_vitals` = 1
            AND pc.`height` IS NOT NULL
            AND pc.`weight` IS NOT NULL
            AND pc.`bmi` IS NOT NULL
            AND pc.`is_deleted` = 0
            AND pc.`is_deleted` = 0
            ORDER BY pc.`chart_date` DESC      
        "
        );

        $sql->execute($stmtParams);
        $result = $sql->fetchAll();
        if(isset($result[0])){
            return $result[0];
        }
        return '';
    }

    public function getPatientLastChartWithVitals($patientId) {
        $conn = $this->getEntityManager()->getConnection();
        $stmtParams = array(
            'patientId' => $patientId
        );
        $sql = $conn->prepare(
            "
               SELECT
                pc.`id` AS patientChartId,
                pc.`height`,
                pc.`weight`,
                pc.`blood_pressure_first` AS bloodPressureFirst,
                pc.`blood_pressure_second` AS bloodPressureSecond,
                pc.`pulse`,
                pc.`temperature`,
                pc.`respiratory_rate` AS respiratoryRate,
                pc.`bmi`,
                pc.`nose`,
                pc.`stop_bang`,
                pc.`neck_size`,
                pc.`smoker`,
                pc.`pre_ess`,
                pc.`otwo_saturation`,
                pc.`drinker`,
                pc.`post_ess`,
                DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') AS chartDate,
                cs.`description` AS chartStatus,
                ct.`description` AS chartType,
                hf.`description` AS healthFacility,
                p.`full_name` AS provider,
                pat.`endorsed_by` AS endorsed
            FROM `patient_chart` pc
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id` 
            LEFT JOIN `patient` pat ON pc.`patient_id` = pat.`id` 
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id` 
            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id` 
            LEFT JOIN `provider` p ON pc.`provider_id` = p.`id`
            LEFT JOIN `schedule` s ON pc.`schedule_id` = s.`id`
            WHERE pc.`patient_id` = :patientId
            AND ct.`has_vitals` = 1
            AND pc.`is_deleted` = 0
            AND (pc.`height` IS NOT NULL
            OR pc.`weight` IS NOT NULL
            OR pc.`blood_pressure_first` IS NOT NULL
            OR pc.`blood_pressure_second` IS NOT NULL
            OR pc.`pulse` IS NOT NULL
            OR pc.`temperature` IS NOT NULL
            OR pc.`respiratory_rate` IS NOT NULL
            OR pc.`bmi` IS NOT NULL)
            ORDER BY pc.`chart_date` DESC      
        "
        );

        $sql->execute($stmtParams);
        $result = $sql->fetchAll();
        if(isset($result[0])){
            return $result[0];
        }
        return '';
    }

    public function getPatientLastChart($patientId) {
        $conn = $this->getEntityManager()->getConnection();
        $stmtParams = array(
            'patientId' => $patientId
        );
        $sql = $conn->prepare(
            "
           SELECT
                pc.`id` AS patientChartId,
                pc.`height`,
                pc.`weight`,
                pc.`blood_pressure_first` AS bloodPressureFirst,
                pc.`blood_pressure_second` AS bloodPressureSecond,
                pc.`pulse`,
                pc.`temperature`,
                pc.`respiratory_rate` AS respiratoryRate,
                pc.`bmi`,
                DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') AS chartDate,
                cs.`description` AS chartStatus,
                ct.`description` AS chartType,
                hf.`description` AS healthFacility,
                p.`full_name` AS provider,
                pat.`endorsed_by` AS endorsed
            FROM `patient_chart` pc
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id` 
            LEFT JOIN `patient` pat ON pc.`patient_id` = pat.`id` 
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id` 
            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id` 
            LEFT JOIN `provider` p ON pc.`provider_id` = p.`id`
            LEFT JOIN `schedule` s ON pc.`schedule_id` = s.`id`
            WHERE pc.`patient_id` = :patientId
            AND pc.`is_deleted` = 0
            ORDER BY pc.`chart_date` DESC      
        "
        );

        $sql->execute($stmtParams);
        $result = $sql->fetchAll();
        if(isset($result[0])){
            return $result[0];
        }
        return '';
    }

    public function reportPatientWithDifferentBranches($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 ') . "ft.`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 ') . "ft.`health_facility_id` = $branchId";
//                        }
//                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors) ";
//                    } else {
//                        $sqlWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 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,
                    p.`full_name` AS patientName,
                    prev_hf.`description` AS prevHealthFacility,
                    latest_hf.`description` AS latestHealthFacility
                FROM `patient` p
                LEFT JOIN `patient_chart` prev_pc ON prev_pc.`id` = (
                    SELECT 
                        prev_pc1.`id`
                    FROM `patient_chart` prev_pc1
                    LEFT JOIN `patient_chart` prev ON prev.`id` = (
                        SELECT pre.`id`
                        FROM `patient_chart` pre
                        WHERE pre.`is_deleted` = 0
                        AND pre.`patient_id` = p.`id`
                        ORDER BY pre.`chart_date` ASC
                        LIMIT 1
                    )
                    WHERE prev_pc1.`is_deleted` = 0
                    AND prev_pc1.`patient_id` = p.`id`
                    AND prev.health_facility_id != prev_pc1.`health_facility_id`
                    ORDER BY prev_pc1.`chart_date` ASC
                    LIMIT 1
                )
                LEFT JOIN `health_facility` prev_hf ON prev_hf.`id` = prev_pc.`health_facility_id`
                LEFT JOIN `patient_chart` latest_pc ON latest_pc.`id` = (
                    SELECT 
                        latest_pc1.`id`
                    FROM `patient_chart` latest_pc1
                    WHERE latest_pc1.`is_deleted` = 0
                    AND latest_pc1.`patient_id` = p.`id`
                    ORDER BY latest_pc1.`chart_date` DESC
                    LIMIT 1
                )
                LEFT JOIN `health_facility` latest_hf ON latest_hf.`id` = latest_pc.`health_facility_id`
                $sqlWhere
                AND p.`is_duplicate` = 0
                AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
                AND p.`full_name` IS NOT NULL
                AND p.`full_name` IS NOT NULL
                AND p.`referral_date` IS NOT NULL
                AND p.`is_test_patient` = 0
                AND p.`referral_date` != '0000-00-00 00:00:00'
                ";

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

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    //DASHBOARD
    public function dashboardEncounterCalls($params) {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = "WHERE pc.`chart_date` != '0000-00-00 00:00:00'
                      AND pc.`patient_id` IS NOT NULL
                      AND pc.`is_deleted` = 0
                      AND p.`is_active` = 1
                      AND s.`schedule_date` >= :currentDate
                      AND ss.`description` != 'Completed'
                      AND IF(pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
                      AND s.`is_deleted` = 0";
        $stmtParams = array();

//        $andWhere .= "WHERE (date(now()) BETWEEN date_add(pc.`chart_date`, INTERVAL -(ct.`num_day`) DAY) AND date_add(pc.`chart_date`, INTERVAL -(2) DAY))
//                      AND pc.`chart_date` != '0000-00-00 00:00:00'
//                      AND pc.`patient_id` IS NOT NULL
//                      AND pc.`is_deleted` = 0
//                      AND p.`is_active` = 1
//                      AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)";
//        $leftJoin = "LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
//                     LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
//                     LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`";

        if(isset($params['encounterTypes']) && $params['encounterTypes'][0] != 'All'){
            $ors = "";
            foreach ($params['encounterTypes'] as $key => $encounterType) {
                $ors .= (empty($ors) ? '' : ' OR ').'s.`chart_type_id` = :ct_id'.$key;
                $stmtParams['ct_id'.$key] = $encounterType;
            }
            $andWhere .= " AND ($ors)";
        }

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

        if(isset($params['date_from']) && isset($params['date_from'])) {
            $andWhere .= " AND s.`schedule_date` BETWEEN :dateFrom AND :dateTo";
            $stmtParams['dateFrom'] =  date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
            $stmtParams['dateTo'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_to'])));
        }

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

//        $sql = $conn->prepare(
//            "
//            SELECT
//                pc.`id` AS patientChartId,
//                p.`full_name` AS fullName,
//                p.`id` AS patientId,
//                CONCAT('[ ' , DATE_FORMAT(pcn.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', pcn.`created_by`, ' ] : ', pcn.`note`) AS note,
//                au.`full_name` AS accountUser,
//                DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') AS chartDate,
//                hf.`description` AS hfDesc,
//                IF(DATE_FORMAT(NOW(), '%m/%d/%Y') > DATE_FORMAT(DATE_ADD(pcn.`created_at`, INTERVAL 1 DAY), '%m/%d/%Y')   , 'red', NULL) noteInterval
//            FROM `patient_chart` pc
//            $leftJoin
//            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`
//            LEFT JOIN `account_user` au ON pc.`account_user_moa_assignment_id` = au.`id`
//            LEFT JOIN (
//                  SELECT pcn.`id`, pcn.`patient_chart_id`, MAX(pcn.`created_at`) AS created_at
//                  FROM patient_chart_note pcn
//                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
//                  GROUP BY pcn.`patient_chart_id`
//             ) pcnt ON pcnt.`patient_chart_id` = pc.`id`
//             LEFT JOIN (
//                  SELECT pcn.`note`, pcn.`patient_chart_id`, pcn.`created_at`, pcn.`created_by`
//                  FROM patient_chart_note pcn
//                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
//                  GROUP BY pcn.`patient_chart_id`, pcn.`created_at`
//             ) pcn ON pcn.`patient_chart_id` = pc.`id` AND pcnt.`created_at` = pcn.`created_at`
//            $andWhere
//            GROUP BY pc.`id`
//            ORDER BY pc.`chart_date` ASC
//        "
//        );

        $sql = $conn->prepare("
                 SELECT
                    s.`id`,
                    pc.`id` AS patientChartId,
                    hf.`description` AS healthFacility,
                    hf.`address` AS healthFacilityAddress,
                    hf.`phone_no` AS healthFacilityPhoneNo,
                    DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y') AS scheduleDate,
                    p.`id` AS patientId,
                    p.`personal_health_no` AS phn,
                    p.`phone_no` AS phoneNoOne,
                    p.`phone_no_two` AS phoneNoTwo,
                    p.`full_name` AS fullName,
                    pr.`full_name` AS clinician,
                    rp.`full_name` AS referringProvider,
                    ct.`description` AS chartType,
                    ss.`description` AS chartStatus,
                    s.`start_time`,
                    s.`end_time`,
                    CONCAT('[ ' , DATE_FORMAT(pcn.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', pcn.`created_by`, ' ] : ', pcn.`note`) AS note
                FROM `schedule` s
                LEFT JOIN `patient` p ON s.`patient_id` = p.`id`
                LEFT JOIN `health_facility` hf ON s.`health_facility_id` = hf.`id`
                LEFT JOIN `patient_chart` pc ON pc.`schedule_id` = s.`id`
                LEFT JOIN `chart_type` ct ON s.`chart_type_id` = ct.`id`
                -- LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                LEFT JOIN `schedule_status` ss ON s.`schedule_status_id` = ss.`id`
                LEFT JOIN `provider` pr ON s.`provider_id` = pr.`id`
                LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`
                LEFT JOIN `patient_referring_provider` prp ON prp.`id` = (
                    SELECT prp1.`id`
                    FROM `patient_referring_provider` prp1
                    WHERE prp1.`is_deleted` = 0
                    AND prp1.`patient_id` = p.`id`
                    AND prp1.`end_date` IS NULL
                    ORDER BY prp1.`start_date` DESC
                    LIMIT 1
                )
                LEFT JOIN `patient_chart_note` pcn ON pcn.`id` = (
                    SELECT pcn1.`id`
                    FROM `patient_chart_note` pcn1
                    WHERE pcn1.`is_deleted` = 0
                    AND pcn1.`patient_chart_id` = pc.`id`
                    ORDER BY pcn1.`updated_at` DESC
                    LIMIT 1
                )
                LEFT JOIN `referring_provider` rp ON prp.`referring_provider_id` = rp.`id`
                $andWhere
                ORDER BY s.`schedule_date` DESC
                ");

        $sql->execute($stmtParams);

        return $sql->fetchAll();

    }

    /*public function dashboardEncounterCallsCount() {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = "WHERE (date(now()) BETWEEN date_add(pc.`chart_date`, INTERVAL -(ct.`num_day`) DAY) AND date_add(pc.`chart_date`, INTERVAL -(2) DAY))
                      AND pc.`chart_date` != '0000-00-00 00:00:00'
                      AND (pc.`is_deleted` = 0 OR pc.`is_deleted` IS NULL)
                      AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
                      AND pc.`is_deleted` = 0
                      AND p.`is_active` = 1";
        $leftJoin = "LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
                     LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                     LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`";
        $sql = $conn->prepare(
            "
            SELECT
                COUNT(DISTINCT pc.`id`) as data
            FROM `patient_chart` pc
            $leftJoin
            LEFT JOIN (
                  SELECT pcn.`id`, pcn.`patient_chart_id`, MAX(pcn.`created_at`) AS created_at
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  GROUP BY pcn.`patient_chart_id`
             ) pcnt ON pcnt.`patient_chart_id` = pc.`id`
             LEFT JOIN (
                  SELECT pcn.`note`, pcn.`patient_chart_id`, pcn.`created_at`
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  GROUP BY pcn.`patient_chart_id`, pcn.`created_at`
             ) pcn ON pcn.`patient_chart_id` = pc.`id` AND pcnt.`created_at` = pcn.`created_at`
            $andWhere

        "
        );

        $sql->execute();

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

    }*/

    public function dashboardForRebooking($params) {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = '';
        $stmtParams = array();

        if(isset($params['encounterTypes']) && $params['encounterTypes'][0] != 'All'){
            $ors = "";
            foreach ($params['encounterTypes'] as $key => $encounterType) {
                $ors .= (empty($ors) ? '' : ' OR ').'pc.`chart_type_id` = :ct_id'.$key;
                $stmtParams['ct_id'.$key] = $encounterType;
            }
            $andWhere .= " AND ($ors)";
        }

        if(isset($params['branch']) && $params['branch'][0] != 'All'){
            $ors = "";
            foreach ($params['branch'] as $key => $branch) {
                $ors .= (empty($ors) ? '' : ' OR ').'pc.`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(pc.`chart_date`) = :month';
            $stmtParams['month'] = $month;
        }

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

        $sql = $conn->prepare(
            "
            SELECT
                pc.`id` AS patientChartId,
                p.`full_name` AS fullName,
                cs.`description` AS chartStatus,
                CONCAT('[ ' , DATE_FORMAT(pnote.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', pnote.`created_by`, ' ] : ', pnote.`note`) AS note,
                p.`id` AS patientId,
                hf.`description` AS healthFacility,
                ct.`description` AS chartType,
                IF(DATE_FORMAT(NOW(), '%m/%d/%Y') > DATE_FORMAT(DATE_ADD(pnote.`created_at`, INTERVAL 1 DAY), '%m/%d/%Y'), 'red', NULL) noteInterval,
                DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') AS chartDate,
                pr.`full_name` AS providerName
            FROM `patient_chart` pc
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`
            LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN (
                  SELECT pct.`id`, pct.`patient_id`, pct.`chart_type_id`, MAX(pct.`chart_date`) AS chart_date
                  FROM `patient_chart` pct
                  WHERE pct.`is_deleted` = 0
                  GROUP BY pct.`patient_id`, pct.`chart_type_id`
            ) pct ON pc.`patient_id` = pct.`patient_id` AND pc.`chart_type_id` = pct.`chart_type_id` 
            LEFT JOIN (
                  SELECT pcn.`id`, pcn.`patient_chart_id`, MAX(pcn.`created_at`) AS created_at
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` =  pc.`id`
                  WHERE pcn.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`
             ) pcnt ON pcnt.`patient_chart_id` = pc.`id`
             LEFT JOIN (
                  SELECT pcn.`note`, pcn.`patient_chart_id`, pcn.`created_at`, pcn.`created_by`
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pcn.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`, pcn.`created_at`
             ) pcn ON pcn.`patient_chart_id` = pc.`id` AND pcnt.`created_at` = pcn.`created_at`
             LEFT JOIN(
                  SELECT pn.`note`, pn.`patient_id` patientId, pn.`created_by`, pn.`created_at`
                  FROM `patient_note` pn
                  WHERE pn.`is_deleted` = 0
                  GROUP BY pn.`created_at`
                  ORDER BY pn.`created_at` DESC
             ) pnote ON pnote.`patientId` = p.`id`
            WHERE pc.`chart_date` != '0000-00-00 00:00:00'
            AND (pc.`is_deleted` = '0' OR pc.`is_deleted` IS NULL)
            AND cs.`description` IN ('No Show', 'Cancelled', 'Rescheduled')
            AND p.`is_active` = 1
            AND pc.`is_deleted` = 0
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
            AND (pct.`id` IS NULL OR pc.`chart_date` >= pct.`chart_date`)
            $andWhere
            GROUP BY pc.`id`
            ORDER BY pc.`chart_date` DESC, hf.`description`
        "
        );

        $sql->execute($stmtParams);

        return $sql->fetchAll();

    }

    public function dashboardForRebookingSummary($params) {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = '';
        $stmtParams = array();

        if(isset($params['branch']) && $params['branch'] != 'All'){
            $ors = "";
            foreach ($params['branch'] as $key => $branch) {
                $ors .= (empty($ors) ? '' : ' OR ').'pc.`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(pc.`chart_date`) = :month';
            $stmtParams['month'] = $month;
        }

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

        $sql = $conn->prepare(
            "
            SELECT
                CONCAT_WS(' : ', EXTRACT(YEAR FROM pc.`chart_date`), CONVERT(COUNT(EXTRACT(YEAR FROM pc.`chart_date`)), INT)) summaryCount
            FROM `patient_chart` pc
            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`
            LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN (
                  SELECT pct.`id`, pct.`patient_id`, pct.`chart_type_id`, MAX(pct.`chart_date`) AS chart_date
                  FROM `patient_chart` pct
                  WHERE pct.`is_deleted` = 0
                  GROUP BY pct.`patient_id`, pct.`chart_type_id`
            ) pct ON pc.`patient_id` = pct.`patient_id` AND pc.`chart_type_id` = pct.`chart_type_id` 
            LEFT JOIN (
                  SELECT pcn.`id`, pcn.`patient_chart_id`, MAX(pcn.`created_at`) AS created_at
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pcn.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`
             ) pcnt ON pcnt.`patient_chart_id` = pc.`id`
             LEFT JOIN (
                  SELECT pcn.`note`, pcn.`patient_chart_id`, pcn.`created_at`, pcn.`created_by`
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pcn.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`, pcn.`created_at`
             ) pcn ON pcn.`patient_chart_id` = pc.`id` AND pcnt.`created_at` = pcn.`created_at`
            WHERE pc.`chart_date` != '0000-00-00 00:00:00'
            AND (pc.`is_deleted` = '0' OR pc.`is_deleted` IS NULL)
            AND cs.`description` IN ('No Show', 'Cancelled', 'Rescheduled')
            AND p.`is_active` = 1
            AND pc.`is_deleted` = 0
            AND EXTRACT(YEAR FROM pc.`chart_date`) >= '2014'
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
            AND (pct.`id` IS NULL OR pc.`chart_date` >= pct.`chart_date`)
            $andWhere
            GROUP BY EXTRACT(YEAR FROM pc.`chart_date`)
        "
        );

        $sql->execute($stmtParams);

        return $sql->fetchAll();

    }

    /*public function dashboardForRebookingCount() {
        $conn = $this->getEntityManager()->getConnection();

        $sql = $conn->prepare(
            "
            SELECT
                pc.`id` AS patientChartId,
                p.`full_name` AS fullName,
                cs.`description` AS chartStatus,
                p.`id` AS patientId
            FROM `patient_chart` pc
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`
            LEFT JOIN (
                  SELECT pct.`id`, pct.`patient_id`, pct.`chart_type_id`, MAX(pct.`chart_date`) AS chart_date
                  FROM `patient_chart` pct 
                  GROUP BY pct.`patient_id`, pct.`chart_type_id`
            ) pct ON pc.`patient_id` = pct.`patient_id` AND pc.`chart_type_id` = pct.`chart_type_id` 
            WHERE pc.`chart_date` != '0000-00-00 00:00:00'
            AND (pc.`is_deleted` = '0' OR pc.`is_deleted` IS NULL)
            AND cs.`description` IN ('No Show', 'Cancelled')
            AND p.`is_active` = '1'
            AND pc.`is_deleted` = 0
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`description` NOT IN ('Declined', 'Booked', 'Completed'), 0=0)
            AND (pct.`id` IS NULL OR pc.`chart_date` >= pct.`chart_date`)
        "
        );

        $sql->execute();

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

    }*/

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

        $sql = $conn->prepare(
            "
            SELECT
                pc.`id` AS patientChartId,
                p.`full_name` AS fullName,
                ct.`description` AS chartType,
                DATE_FORMAT(pc.`chart_date`, '%m-%d-%Y') AS chartDate,
                p.`id` AS patientId
            FROM `patient_chart` pc
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id` 
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id` 
            WHERE ct.`description` = 'Booked'
            AND pc.`chart_date` != '0000-00-00 00:00:00'
            AND pc.`patient_id` IS NOT NULL
            AND (pc.`is_done` = '0' OR pc.`is_done` = NULL)
            AND pc.`is_deleted` = 0
            AND p.`is_active` = '1'
            $andWhere
        "
        );

        $sql->execute($stmtParams);

        return $sql->fetchAll();

    }

    /*public function dashboardAppointmentNoticeCount() {
        $conn = $this->getEntityManager()->getConnection();

        $sql = $conn->prepare(
            "
            SELECT
                COUNT(DISTINCT pc.`id`) AS data
            FROM `patient_chart` pc
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id` 
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id` 
            WHERE ct.`description` = 'Booked'
            AND pc.`chart_date` != '0000-00-00 00:00:00'
            AND pc.`patient_id` IS NOT NULL
            AND (pc.`is_done` = '0' OR pc.`is_done` = NULL)
            AND pc.`is_deleted` = 0
            AND p.`is_active` = '1'
        "
        );

        $sql->execute();

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

    }*/

    public function dashboardForSeventyTwoHourFollowUps($params) {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = '';
        $stmtParams = array();

        if(isset($params['encounterTypes']) && $params['encounterTypes'][0] != 'All'){
            $ors = "";
            foreach ($params['encounterTypes'] as $key => $encounterType) {
                $ors .= (empty($ors) ? '' : ' OR ').'pc.`chart_type_id` = :ct_id'.$key;
                $stmtParams['ct_id'.$key] = $encounterType;
            }
            $andWhere .= " AND ($ors)";
        }


        if(isset($params['branch']) && $params['branch'][0] != 'All'){
            $ors = "";
            foreach ($params['branch'] as $key => $branch) {
                $ors .= (empty($ors) ? '' : ' OR ').'pc.`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(pc.`chart_date`) = :month';
            $stmtParams['month'] = $month;
        }

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

        $sql = $conn->prepare(
            "
            SELECT
                pc.`id` AS patientChartId,
                p.`full_name` AS fullName,
                ct.`description` AS chartType,
                DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') AS chartDate,
                p.`id` AS patientId,
                hf.`description` AS healthFacility,
                pr.`full_name` AS providerName,
                pctm.`current_ahi` AS currentAhi,
                DATE_FORMAT(pccs.`chart_date`, '%m/%d/%Y') AS cpapSetupDate,
	            cscs.`description` AS cpapSetupStatus,
	            p_pr.`full_name` AS primaryProvider,
	            CONCAT('[ ' , DATE_FORMAT(pcn.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', pcn.`created_by`, ' ] : ', pcn.`note`) AS note
            FROM `patient_chart` pc
            LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id` 
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id` 
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
            LEFT JOIN `patient_chart_type_method` pctm ON pc.`id` = pctm.`patient_chart_id`
            LEFT JOIN `patient_chart` pccs ON pccs.`id` = (
                SELECT pccs1.`id`
                FROM `patient_chart` pccs1
                LEFT JOIN `chart_type` ctcs1 ON pccs1.`chart_type_id` = ctcs1.`id`
                WHERE pccs1.`is_deleted` = 0
                AND ctcs1.`description` = 'CPAP Setup'
                AND pccs1.`patient_id` = pc.`patient_id`
                LIMIT 1
            )
            LEFT JOIN `patient_chart` pc_c ON pc_c.`id` = (
                SELECT pc_c1.`id`
                FROM `patient_chart` pc_c1
                LEFT JOIN `chart_type` cs_c1 ON pc_c1.`chart_type_id` = cs_c1.`id`
                WHERE pc_c1.`is_deleted` = 0
                AND cs_c1.`description` = 'HSAT'
                AND pc_c1.`patient_id` = p.`id`
                LIMIT 1
            )
            LEFT JOIN `provider` p_pr ON pc_c.`provider_id` = p_pr.`id`
            LEFT JOIN (
                  SELECT pcn.`id`, pcn.`patient_chart_id`, MAX(pcn.`created_at`) AS created_at
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pcn.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`
            ) pcnt ON pcnt.`patient_chart_id` = pc.`id`
            LEFT JOIN (
                  SELECT pcn.`note`, pcn.`patient_chart_id`, pcn.`created_at`, pcn.`created_by`
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pc.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`, pcn.`created_at`
            ) pcn ON pcn.`patient_chart_id` = pc.`id` AND pcnt.`created_at` = pcn.`created_at`
            LEFT JOIN `chart_status` cscs ON pccs.`chart_status_id` = cscs.`id`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id` 
            WHERE pc.`chart_date` != '0000-00-00 00:00:00'
            AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0)
            AND DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') BETWEEN DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') 
            AND DATE_FORMAT(DATE_ADD(pc.`chart_date`, INTERVAL 3 DAY), '%m/%d/%Y')  
            AND ct.`description` = 'CPAP Setup'
            AND cs.`description` = 'Completed' 
            AND p.`is_active` = 1
            AND pc.`is_deleted` = 0
            AND IF(ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
            $andWhere
        "

        );

        $sql->execute($stmtParams);

        return $sql->fetchAll();

    }

    public function dashboardForSeventyTwoHourFollowUpsSummary($params) {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = '';
        $stmtParams = array();
        if(isset($params['branch']) && $params['branch'][0] != 'All'){
            $ors = "";
            foreach ($params['branch'] as $key => $branch) {
                $ors .= (empty($ors) ? '' : ' OR ').'pc.`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(pc.`chart_date`) = :month';
            $stmtParams['month'] = $month;
        }

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

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

        );

        $sql->execute($stmtParams);

        return $sql->fetchAll();

    }

    /*public function dashboardForSeventyTwoHourFollowUpsCount() {
        $conn = $this->getEntityManager()->getConnection();

        $sql = $conn->prepare(
            "
            SELECT
                COUNT(DISTINCT pc.`id`) AS data
            FROM `patient_chart` pc
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id` 
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id` 
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id` 
            WHERE ct.`description` = '72 Hour Follow-up'
            AND (pc.`is_done` = '0' OR pc.`is_done` = NULL)
            AND IF (ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
            AND p.`is_active` = '1'
            AND pc.`is_deleted` = 0
        "

        );

        $sql->execute();

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

    }*/

    public function dashboardForThreeMonthsFollowUps($params) {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = '';
        $stmtParams = array();

        if(isset($params['encounterTypes']) && $params['encounterTypes'][0] != 'All'){
            $ors = "";
            foreach ($params['encounterTypes'] as $key => $encounterType) {
                $ors .= (empty($ors) ? '' : ' OR ').'pc.`chart_type_id` = :ct_id'.$key;
                $stmtParams['ct_id'.$key] = $encounterType;
            }
            $andWhere .= " AND ($ors)";
        }


        if(isset($params['branch']) && $params['branch'][0] != 'All'){
            $ors = "";
            foreach ($params['branch'] as $key => $branch) {
                $ors .= (empty($ors) ? '' : ' OR ').'pc.`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(pc.`chart_date`) = :month';
            $stmtParams['month'] = $month;
        }

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

        $sql = $conn->prepare(
            "
            SELECT
                pc.`id` AS patientChartId,
                p.`full_name` AS fullName,
                DATE_FORMAT(pccc.`chart_date`, '%m/%d/%Y') AS careCallDate,
                cscc1.`description` AS careCallStatus,
                DATE_FORMAT(pccs.`chart_date`, '%m/%d/%Y') AS cpapSetupDate,
                cscs.`description` AS cpapSetupStatus,
                p_pr.`full_name` AS primaryProvider,
                ct.`description` AS chartType,
                DATE_FORMAT(pc.`chart_date`, '%m-%d-%Y') AS chartDate,
                p.`id` AS patientId,
                hf.`description` AS healthFacility,
                CONCAT('[ ' , DATE_FORMAT(pcn.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', pcn.`created_by`, ' ] : ', pcn.`note`) AS note
            FROM `patient` p
            LEFT JOIN (
                SELECT
                  pc.`patient_id`,
                  MAX(pc.`chart_date`) AS chart_date
                FROM `patient_chart` pc
                LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                WHERE pc.`chart_date` != '0000-00-00 00:00:00'
                AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0)
                AND ct.`description` IN ('CPAP Setup', '3 Month Follow-up')
                AND IF (ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
                AND p.`is_active` = 1
                $andWhere
                GROUP BY pc.`patient_id`
            ) pc_max ON p.`id` = pc_max.`patient_id`
            LEFT JOIN `patient_chart` pc ON pc_max.`patient_id` = pc.`patient_id` AND pc_max.`chart_date` = pc.`chart_date` AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0) AND DATE(now()) >= DATE_ADD(DATE_FORMAT(pc.`chart_date`, '%Y-%m-%d'), INTERVAL (3) MONTH)
            LEFT JOIN `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` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN (
                  SELECT pcn.`id`, pcn.`patient_chart_id`, MAX(pcn.`created_at`) AS created_at
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pcn.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`
            ) pcnt ON pcnt.`patient_chart_id` = pc.`id`
            LEFT JOIN (
                  SELECT pcn.`note`, pcn.`patient_chart_id`, pcn.`created_at`, pcn.`created_by`
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pc.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`, pcn.`created_at`
            ) pcn ON pcn.`patient_chart_id` = pc.`id` AND pcnt.`created_at` = pcn.`created_at`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id` 
            LEFT JOIN `patient_chart` pccc ON pccc.`id` = (
                SELECT pccc1.`id`
                FROM `patient_chart` pccc1
                LEFT JOIN `chart_type` ctcc1 ON pccc1.`chart_type_id` = ctcc1.`id`
                WHERE pccc1.`is_deleted` = 0
                AND ctcc1.`description` = 'Care Call'
                AND pccc1.`patient_id` = p.`id`
                LIMIT 1
            )
            LEFT JOIN `chart_status` cscc1 ON pccc.`chart_status_id` = cscc1.`id`
            LEFT JOIN `patient_chart` pccs ON pccs.`id` = (
                SELECT pccs1.`id`
                FROM `patient_chart` pccs1
                LEFT JOIN `chart_type` ctcs1 ON pccs1.`chart_type_id` = ctcs1.`id`
                WHERE pccs1.`is_deleted` = 0
                AND ctcs1.`description` = 'CPAP Setup'
                AND pccs1.`patient_id` = p.`id`
                LIMIT 1
            )
            LEFT JOIN `chart_status` cscs ON pccs.`chart_status_id` = cscs.`id`
            LEFT JOIN `patient_chart` pc_c ON pc_c.`id` = (
                SELECT pc_c1.`id`
                FROM `patient_chart` pc_c1
                LEFT JOIN `chart_type` cs_c1 ON pc_c1.`chart_type_id` = cs_c1.`id`
                WHERE pc_c1.`is_deleted` = 0
                AND cs_c1.`description` = 'HSAT'
                AND pc_c1.`patient_id` = p.`id`
                LIMIT 1
            )
            LEFT JOIN `provider` p_pr ON pc_c.`provider_id` = p_pr.`id`
            WHERE pc.`id` IS NOT NULL
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
            $andWhere
            ORDER BY pc.`chart_date` DESC, hf.`description`
        "

        );

        $sql->execute($stmtParams);

        return $sql->fetchAll();

    }

    public function dashboardForThreeMonthsFollowUpsSummary($params) {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = '';
        $stmtParams = array();
        if(isset($params['branch']) && $params['branch'][0] != 'All'){
            $ors = "";
            foreach ($params['branch'] as $key => $branch) {
                $ors .= (empty($ors) ? '' : ' OR ').'pc.`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(pc.`chart_date`) = :month';
            $stmtParams['month'] = $month;
        }

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

        $sql = $conn->prepare(
            "
            SELECT
                CONCAT_WS(' : ', EXTRACT(YEAR FROM pc.`chart_date`), CONVERT(COUNT(EXTRACT(YEAR FROM pc.`chart_date`)), INT)) summaryCount
            FROM `patient` p
            LEFT JOIN (
                SELECT
                  pc.`patient_id`,
                  MAX(pc.`chart_date`) AS chart_date
                FROM `patient_chart` pc
                LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                WHERE pc.`chart_date` != '0000-00-00 00:00:00'
                AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0)
                AND ct.`description` IN ('CPAP Setup', '3 Month Follow-up')
                AND IF (ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
                AND p.`is_active` = 1
                $andWhere
                GROUP BY pc.`patient_id`
            ) pc_max ON p.`id` = pc_max.`patient_id`
            LEFT JOIN `patient_chart` pc ON pc_max.`patient_id` = pc.`patient_id` AND pc_max.`chart_date` = pc.`chart_date` AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0) AND DATE(now()) >= DATE_ADD(DATE_FORMAT(pc.`chart_date`, '%Y-%m-%d'), INTERVAL (3) MONTH)
            LEFT JOIN `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` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id` 
            WHERE pc.`id` IS NOT NULL
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
            AND EXTRACT(YEAR FROM pc.`chart_date`) >= '2014'
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
            $andWhere
            GROUP BY EXTRACT(YEAR FROM pc.`chart_date`)
        "

        );

        $sql->execute($stmtParams);

        return $sql->fetchAll();

    }

    /*public function dashboardForThreeMonthsFollowUpsCount() {
        $conn = $this->getEntityManager()->getConnection();

        $sql = $conn->prepare(
            "
            SELECT
                COUNT(DISTINCT pc.`id`) AS data
            FROM `patient_chart` pc
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id` 
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id` 
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id` 
            WHERE ct.`description` = '3 Month Follow-up'
            AND pc.`chart_date` != '0000-00-00 00:00:00'
            AND pc.`patient_id` IS NOT NULL
            AND (pc.`is_done` = '0' OR pc.`is_done` = NULL)
            AND IF (ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
            AND p.`is_active` = '1'
            AND pc.`is_deleted` = 0
        "

        );

        $sql->execute();

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

    }*/

    public function dashboardForSixMonthsFollowUps($params) {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = '';
        $stmtParams = array();

        if(isset($params['encounterTypes']) && $params['encounterTypes'][0] != 'All'){
            $ors = "";
            foreach ($params['encounterTypes'] as $key => $encounterType) {
                $ors .= (empty($ors) ? '' : ' OR ').'pc.`chart_type_id` = :ct_id'.$key;
                $stmtParams['ct_id'.$key] = $encounterType;
            }
            $andWhere .= " AND ($ors)";
        }

        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(pc.`chart_date`) = :month';
            $stmtParams['month'] = $month;
        }

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

        $sql = $conn->prepare(
            "
            SELECT
                pc.`id` AS patientChartId,
                p.`id` AS patientId,
                hf.`description` AS healthFacility,
                p.`full_name` AS fullName,
                DATE_FORMAT(pcsix.`chart_date`, '%m/%d/%Y') AS sixMonthFFdate,
                cssix.`description` AS sixMonthFFstatus,
                DATE_FORMAT(pccs.`chart_date`, '%m/%d/%Y') AS cpapSetupDate,
                cscs.`description` AS cpapSetupStatus,   
                DATE_FORMAT(pccss.`chart_date`, '%m/%d/%Y') AS cpapSetupSoldDate,
                cscss.`description` AS cpapSetupSoldStatus,   
                DATE_FORMAT(pccee.`chart_date`, '%m/%d/%Y') AS cpaEndEvalDate,
                cscee.`description` AS cpapEndEvalStatus,   
                p_pr.`full_name` AS primaryProviderName,
                IF(pcsix.`chart_date` IS NOT NULL, DATE_FORMAT(DATE_ADD(pccss.`chart_date`, INTERVAL 6 MONTH), '%m/%d/%Y'), pccss.`chart_date`) AS sixMonthFromCpap,
                CONCAT('[ ' , DATE_FORMAT(pcn.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', pcn.`created_by`, ' ] : ', pcn.`note`) AS note,
                IF(DATE_FORMAT(NOW(), '%m/%d/%Y') > DATE_FORMAT(DATE_ADD(pcn.`created_at`, INTERVAL 1 DAY), '%m/%d/%Y')   , 'red', NULL) noteInterval
            FROM `patient` p
            LEFT JOIN `health_facility` hf ON p.`health_facility_id` = hf.`id`
            LEFT JOIN (
                SELECT
                  pc.`patient_id`,
                  MAX(pc.`chart_date`) AS chart_date
                FROM `patient_chart` pc
                LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                WHERE pc.`chart_date` != '0000-00-00 00:00:00'
                AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0)
                AND ct.`description` IN ('CPAP Setup', '6 Month Follow-up')
                AND IF (ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
                AND p.`is_active` = 1
                AND pc.`is_deleted` = 0
                $andWhere
                GROUP BY pc.`patient_id`
            ) pc_max ON p.`id` = pc_max.`patient_id`
            LEFT JOIN `patient_chart` pc ON pc_max.`patient_id` = pc.`patient_id` 
                AND pc_max.`chart_date` = pc.`chart_date` 
                AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0) 
                AND DATE(NOW()) >= DATE_ADD(DATE_FORMAT(pc.`chart_date`, '%Y-%m-%d'), INTERVAL (6) MONTH)
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN (
                  SELECT pcn.`id`, pcn.`patient_chart_id`, MAX(pcn.`created_at`) AS created_at
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pcn.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`
             ) pcnt ON pcnt.`patient_chart_id` = pc.`id`
             LEFT JOIN (
                  SELECT pcn.`note`, pcn.`patient_chart_id`, pcn.`created_at`, pcn.`created_by`
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pc.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`, pcn.`created_at`
             ) pcn ON pcn.`patient_chart_id` = pc.`id` AND pcnt.`created_at` = pcn.`created_at`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id` 
            LEFT JOIN `patient_chart` pcsix ON pcsix.`id` = (
                SELECT pcsix1.`id`
                FROM `patient_chart` pcsix1
                LEFT JOIN `chart_type` ctsix1 ON pcsix1.`chart_type_id` = ctsix1.`id`
                WHERE pcsix1.`is_deleted` = 0
                AND ctsix1.`description` = '6 Month Follow-up'
                AND pcsix1.`patient_id` = p.`id`
                LIMIT 1
            )
            LEFT JOIN `chart_status` cssix ON pcsix.`chart_status_id` = cssix.`id`
            LEFT JOIN `patient_chart` pccs ON pccs.`id` = (
                SELECT pccs1.`id`
                FROM `patient_chart` pccs1
                LEFT JOIN `chart_type` ctcs1 ON pccs1.`chart_type_id` = ctcs1.`id`
                WHERE pccs1.`is_deleted` = 0
                AND ctcs1.`description` = 'CPAP Setup'
                AND pccs1.`patient_id` = p.`id`
                LIMIT 1
            )
            LEFT JOIN `chart_status` cscs ON pccs.`chart_status_id` = cscs.`id`
            LEFT JOIN `patient_chart` pccss ON pccss.`id` = (
                SELECT pccss1.`id`
                FROM `patient_chart` pccss1
                LEFT JOIN `chart_type` ctcss1 ON pccss1.`chart_type_id` = ctcss1.`id`
                WHERE pccss1.`is_deleted` = 0
                AND ctcss1.`description` = 'CPAP Setup Sold'
                AND pccss1.`patient_id` = p.`id`
                LIMIT 1
            )
            LEFT JOIN `chart_status` cscss ON pccss.`chart_status_id` = cscss.`id`
             LEFT JOIN `patient_chart` pccee ON pccee.`id` = (
                SELECT pccee1.`id`
                FROM `patient_chart` pccee1
                LEFT JOIN `chart_type` ctcee1 ON pccee1.`chart_type_id` = ctcee1.`id`
                WHERE pccee1.`is_deleted` = 0
                AND ctcee1.`description` = 'CPAP End Eval'
                AND pccee1.`patient_id` = p.`id`
                LIMIT 1
            )
            LEFT JOIN `chart_status` cscee ON pccee.`chart_status_id` = cscee.`id`
            LEFT JOIN `patient_chart` pc_c ON pc_c.`id` = (
                SELECT pc_c1.`id`
                FROM `patient_chart` pc_c1
                LEFT JOIN `chart_type` cs_c1 ON pc_c1.`chart_type_id` = cs_c1.`id`
                WHERE pc_c1.`is_deleted` = 0
                AND cs_c1.`description` = 'HSAT'
                AND pc_c1.`patient_id` = p.`id`
                LIMIT 1
            )
            LEFT JOIN `provider` p_pr ON pc_c.`provider_id` = p_pr.`id`
            WHERE pc.`id` IS NOT NULL
            AND ct.`description` IN ('CPAP Setup', '6 Month Follow-up')
            AND IF (ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
            $andWhere
            ORDER BY pc.`chart_date` DESC, hf.`description`
        "

        );

        $sql->execute($stmtParams);

        return $sql->fetchAll();

    }

    public function dashboardForSixMonthsFollowUpsSummary($params) {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = '';
        $stmtParams = array();

        if(isset($params['branch'])) {
            if ($params['branch'] == 'All') {
                $andWhere = '';
            } else {
                $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(pc.`chart_date`) = :month';
            $stmtParams['month'] = $month;
        }

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

        $sql = $conn->prepare(
            "
            SELECT
                CONCAT_WS(' : ', EXTRACT(YEAR FROM pc.`chart_date`), CONVERT(COUNT(EXTRACT(YEAR FROM pc.`chart_date`)), INT)) summaryCount
            FROM `patient` p
            LEFT JOIN `health_facility` hf ON p.`health_facility_id` = hf.`id`
            LEFT JOIN (
                SELECT
                  pc.`patient_id`,
                  MAX(pc.`chart_date`) AS chart_date
                FROM `patient_chart` pc
                LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                WHERE pc.`chart_date` != '0000-00-00 00:00:00'
                AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0)
                AND ct.`description` IN ('CPAP Setup', '6 Month Follow-up')
                AND IF (ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
                AND p.`is_active` = 1
                AND pc.`is_deleted` = 0
                $andWhere
                GROUP BY pc.`patient_id`
            ) pc_max ON p.`id` = pc_max.`patient_id`
            LEFT JOIN `patient_chart` pc ON pc_max.`patient_id` = pc.`patient_id` 
                AND pc_max.`chart_date` = pc.`chart_date` 
                AND (pc.`is_deleted` IS NULL OR pc.`is_deleted` = 0) 
                AND DATE(NOW()) >= DATE_ADD(DATE_FORMAT(pc.`chart_date`, '%Y-%m-%d'), INTERVAL (6) MONTH)
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN (
                  SELECT pcn.`id`, pcn.`patient_chart_id`, MAX(pcn.`created_at`) AS created_at
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pcn.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`
             ) pcnt ON pcnt.`patient_chart_id` = pc.`id`
             LEFT JOIN (
                  SELECT pcn.`note`, pcn.`patient_chart_id`, pcn.`created_at`, pcn.`created_by`
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pc.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`, pcn.`created_at`
             ) pcn ON pcn.`patient_chart_id` = pc.`id` AND pcnt.`created_at` = pcn.`created_at`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id` 
            WHERE pc.`id` IS NOT NULL
            AND ct.`description` IN ('CPAP Setup', '6 Month Follow-up')
                AND IF (ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
            AND EXTRACT(YEAR FROM pc.`chart_date`) >= '2014'
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
            $andWhere
            GROUP BY EXTRACT(YEAR FROM pc.`chart_date`)
        "

        );

        $sql->execute($stmtParams);

        return $sql->fetchAll();

    }

    /*public function dashboardForSixMonthsFollowUpsCount() {
        $conn = $this->getEntityManager()->getConnection();
        $stmt = $conn->prepare("
            
            CREATE TEMPORARY TABLE IF NOT EXISTS `patient_chart_temp`
            (INDEX pct_index_temp (`patient_id`))
             AS ( 
              SELECT
                  pc.`id`,
                  pc.`chart_date`,
                  pc.`is_done`,
                  pc.`chart_type_id`,
                  pc.`chart_status_id`,
                  ct.`description` AS chartType,
                  pc.`patient_id`
              FROM `patient_chart` pc
              LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id` 
              LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id` 
              WHERE ct.`description` IN ('CPAP Setup', '6 Month Follow-up')
              -- AND date(now()) >= date_add(DATE_FORMAT(pc.`chart_date`, '%Y-%m-%d'), INTERVAL (6) MONTH)
              AND IF (ct.`description` = 'CPAP Setup', cs.`description` != 'Declined', 1=1)
              AND pc.`is_deleted` = 0
              ORDER BY pc.`chart_date` DESC
            );
            
        ");
        $stmt->execute();
        $sql = $conn->prepare(
            "
            SELECT
                COUNT(DISTINCT pc.`id`) AS data
            FROM `patient` p
            LEFT JOIN (
                    SELECT *
                    FROM `patient_chart_temp`
                    GROUP BY `patient_id`
            ) pc ON p.`id` = pc.`patient_id` AND date(now()) >= date_add(DATE_FORMAT(pc.`chart_date`, '%Y-%m-%d'), INTERVAL (6) MONTH)
            WHERE pc.`chart_date` != '0000-00-00 00:00:00'
            AND pc.`patient_id` IS NOT NULL
            AND (pc.`is_done` = '0' OR pc.`is_done` = NULL)
            AND pc.`id` IS NOT NULL
            AND p.`is_active` = '1'
            AND p.`is_deleted` = 0
        "

        );

        $sql->execute();

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

    }*/

    public function dashboardMyTasks($params, $userData) {
        $conn = $this->getEntityManager()->getConnection();
        $andWhere = '';
        $stmtParams = array();
        if(isset($params['branch']) && $params['branch'] != 'All'){
            $ors = "";
            if(is_array($params['branch'])){
                foreach ($params['branch'] as $key => $branch) {
                    $ors .= (empty($ors) ? '' : ' OR ').'pc.`health_facility_id` = :hf_id'.$key;
                    $stmtParams['hf_id'.$key] = $branch;
                }
            } else {
                $ors .= 'pc.`health_facility_id` = :hf_id';
                $stmtParams['hf_id'] = $params['branch'];
            }
            $andWhere .= " AND ($ors)";
        }
        if(isset($userData['id']) && $userData['type'] != 'Super Admin'){
            $andWhere .= " AND pc.`account_user_moa_assignment_id` = :userId";
            $stmtParams['userId'] = $userData['id'];
        }
        $sql = $conn->prepare(
            "
            SELECT
                pc.`id` AS patientChartId,
                p.`id` AS patientId,
                DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y') AS chartDate,
                hf.`description` AS healthFacility,
                p.`full_name` AS patient,
                pr.`full_name` AS provider,
                ct.`description` AS chartType,
                cs.`description` AS chartStatus,
                pcn.`note` AS latestNote
            FROM `patient_chart` pc
            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
            LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN (
                  SELECT pcn.`id`, pcn.`patient_chart_id`, MAX(pcn.`created_at`) AS created_at
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  GROUP BY pcn.`patient_chart_id`
             ) pcnt ON pcnt.`patient_chart_id` = pc.`id`
             LEFT JOIN (
                  SELECT pcn.`note`, pcn.`patient_chart_id`, pcn.`created_at`
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  GROUP BY pcn.`patient_chart_id`, pcn.`created_at`
             ) pcn ON pcn.`patient_chart_id` = pc.`id` AND pcnt.`created_at` = pcn.`created_at`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`
            WHERE pc.`is_deleted` = 0
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
            AND pc.`patient_id` IS NOT NULL
            AND cs.`description` != 'Deleted'
            $andWhere
        "
        );

        $sql->execute($stmtParams);

        return $sql->fetchAll();

    }

    public function reportBookedperEncounterReport($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.`updated_at` 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['accountUser']) && !empty($params['accountUser']) && 'NULL' !== strtoupper($params['accountUser'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "au.`id` = :accountUser";
            $stmtParams['accountUser'] = $params['accountUser'];
        }

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

        $sql = "SELECT 
                    au.`full_name` accountUser,
                    ct.`description` encounterType,
                    COUNT(cs.`description`) encounterStatusCount
                FROM `patient_chart` pc
                LEFT JOIN `patient` pat ON pc.`patient_id` = pat.`id`
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                LEFT JOIN `account_user` au ON au.`username` = pc.`updated_by`
                $sqlWhere
                AND au.`id` IS NOT NULL
                GROUP BY ct.`id`, au.`id`
                ORDER BY COUNT(cs.`id`) DESC";

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

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    public function managementDashboard_daily($params) {
        $sqlWhere = "WHERE  (p.`is_deleted` = 0 OR p.`is_deleted` IS NOT NULL)";
        $sqlWhere_referral = "";
        $sqlWhere_booked = "";
        $sqlWhere_callBack = "";
        $sqlWhere_voiceMail = "";

        //STMTPARAMS
        $stmtParams_referral = array();
        $stmtParams_status = array();
        $stmtParams_referral['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));

        if($params['branch'] != null && $params['branch'] != '' && $params['branch'] != 'null') {
            $sqlWhere_referral .= " AND phf.`health_facility_id` = :branch";
            $stmtParams_referral['branch'] = $params['branch'];
        }

        if($params['company'] != null && $params['company'] != '' && $params['company'] != 'null') {
            $sqlWhere_referral .= " AND hf.`company_id` = :company";
            $stmtParams_referral['company'] = $params['company'];
        }

        if($params['provider'] != null && $params['provider'] != '' && $params['provider'] != 'null') {
            $sqlWhere_booked = " AND sc.`provider_id` = :bookedProvider";
            $sqlWhere_callBack = " AND pc2.`provider_id` = :callBackProvider";
            $sqlWhere_voiceMail = " AND pc3.`provider_id` = :voiceMailProvider";
            $stmtParams_status['bookedProvider'] = $params['provider'];
            $stmtParams_status['callBackProvider'] = $params['provider'];
            $stmtParams_status['voiceMailProvider'] = $params['provider'];
        }

        $stmtParams_status['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
        $stmtParams_cpapSetup['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
        $stmtParams_branchStatus['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));

        $sql_referralCount = "SELECT 
                                 phf.`health_facility_id` AS branchId,
                                 hf.`description` AS healthFacilityDesc,
                                 COUNT(DISTINCT p.`id`) AS dailyReferralCount
                               FROM `patient` p
                               LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                                      SELECT
                                          phf1.`id`
                                       FROM `patient_health_facility` phf1
                                       WHERE phf1.`end_date` IS NULL
                                       AND phf1.`patient_id` = p.`id`
                                       ORDER BY phf1.`created_at` DESC
                                       LIMIT 1
                               )
                               LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                               LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                               WHERE p.`referral_date` = :dateFrom
                               $sqlWhere_referral
                               AND p.`is_deleted` = 0
                               AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
                               AND hf.`description` IS NOT NULL
                               AND p.`is_duplicate` = 0
                               AND hf.`description` NOT IN('DemoLocation', 'Demo_Test')
                               GROUP BY phf.`health_facility_id`";

        $sql_statusCount = "SELECT 
                                COUNT(DISTINCT sched_booked.`id`) dailyBookedCount,
                                COUNT(DISTINCT pc_callback.`id`) dailyCallBackCount,
                                COUNT(DISTINCT pc_voicemail.`id`) dailyVoiceMailCount
                            FROM `patient` p
                            LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                                      SELECT
                                          phf1.`id`
                                       FROM `patient_health_facility` phf1
                                       WHERE phf1.`end_date` IS NULL
                                       AND phf1.`patient_id` = p.`id`
                                       ORDER BY phf1.`created_at` DESC
                                       LIMIT 1
                            )
                            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                            LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                            LEFT JOIN `schedule` sched_booked ON sched_booked.`id` = (
                                SELECT sc.`id`
                                FROM `schedule` sc
                                LEFT JOIN `schedule_status` ss ON sc.`schedule_status_id` = ss.`id`
                                WHERE (sc.`is_deleted` = 0 OR sc.`is_deleted` IS NOT NULL)
                                AND sc.`patient_id` = p.`id`
                                AND ss.`description` = 'Booked'
                                AND DATE_FORMAT(sc.`created_at`, '%Y-%m-%d') = DATE_FORMAT(:dateFrom, '%Y-%m-%d')
                                $sqlWhere_booked
                                AND sc.`provider_id` IN (
                                   SELECT
                                      p.`id`
                                   FROM `provider_health_facility` phf
                                   LEFT JOIN `provider` p ON phf.`provider_id` = p.`id`
                                   AND phf.`is_deleted` = 0
                                )
                                LIMIT 1
                            )
                            LEFT JOIN `patient_chart` pc_callback ON pc_callback.`id` = (
                                SELECT pc2.`id`
                                FROM `patient_chart` pc2
                                LEFT JOIN `chart_type` ct2 ON pc2.`chart_type_id` = ct2.`id`
                                LEFT JOIN `internal_status` is2 ON pc2.`internal_status_id` = is2.`id`
                                WHERE (pc2.`is_deleted` = 0 OR pc2.`is_deleted` IS NOT NULL)
                                AND pc2.`patient_id` = p.`id`
                                $sqlWhere_callBack
                                AND is2.`description` = 'Call Back'
                                AND DATE_FORMAT(pc2.`updated_at`, '%Y-%m-%d') = DATE_FORMAT(:dateFrom, '%Y-%m-%d')
                                LIMIT 1
                            )
                            LEFT JOIN `patient_chart` pc_voicemail ON pc_voicemail.`id` = (
                                SELECT pc3.`id`
                                FROM `patient_chart` pc3
                                LEFT JOIN `chart_type` ct3 ON pc3.`chart_type_id` = ct3.`id`
                                LEFT JOIN `internal_status` is3 ON pc3.`internal_status_id` = is3.`id`
                                WHERE (pc3.`is_deleted` = 0 OR pc3.`is_deleted` IS NOT NULL)
                                AND pc3.`patient_id` = p.`id`
                                $sqlWhere_voiceMail
                                AND is3.`description` = 'Voice Mail'
                                AND DATE_FORMAT(pc3.`updated_at`, '%Y-%m-%d') = DATE_FORMAT(:dateFrom, '%Y-%m-%d')
                                LIMIT 1
                            )
                            $sqlWhere";

        $sql_branchStatusCount = "SELECT
                                      hf.`id` AS health_facility_id,
                                      hf.`description` AS health_facility_desc,
                                      ct.`id` AS chart_type_id,
                                      hf.`no_of_clinician` AS noOfClinician,
                                      ct.`description` AS chartType,
                                      ct.`daily_target_per_clinician` AS dailyTarget,
                                      SUM(IF(sc1.`description` = 'Completed', 1, 0)) AS completedCount,
                                      SUM(IF(sc2.`description` = 'Cancelled', 1, 0)) AS cancelledCount,
                                      SUM(IF(sc3.`description` = 'No Show', 1, 0)) AS noShowCount
                                    FROM `schedule` sch
                                    LEFT JOIN `chart_type` ct ON sch.`chart_type_id` = ct.`id`
                                    LEFT JOIN `health_facility` hf ON sch.`health_facility_id` = hf.`id`
                                    LEFT JOIN `schedule` sched_completed ON sched_completed.`id` = (
                                        SELECT sc1.`id`
                                        FROM `schedule` sc1
                                        LEFT JOIN `schedule_status` ss1 ON sc1.`schedule_status_id` = ss1.`id`
                                        WHERE (sc1.`is_deleted` = 0 OR sc1.`is_deleted` IS NOT NULL)
                                        AND sc1.`id` = sch.`id`
                                        -- AND ss1.`description` = 'Completed'
                                        AND DATE_FORMAT(sc1.`created_at`, '%Y-%m-%d') = DATE_FORMAT(:dateFrom, '%Y-%m-%d')
                                        LIMIT 1
                                    )
                                    LEFT JOIN `schedule` sched_cancelled ON sched_cancelled.`id` = (
                                        SELECT sc2.`id`
                                        FROM `schedule` sc2
                                        LEFT JOIN `schedule_status` ss2 ON sc2.`schedule_status_id` = ss2.`id`
                                        WHERE (sc2.`is_deleted` = 0 OR sc2.`is_deleted` IS NOT NULL)
                                        AND sc2.`id` = sch.`id`
                                        -- AND ss2.`description` = 'Completed'
                                        AND DATE_FORMAT(sc2.`created_at`, '%Y-%m-%d') = DATE_FORMAT(:dateFrom, '%Y-%m-%d')
                                        LIMIT 1
                                    )
                                    LEFT JOIN `schedule` sched_no_show ON sched_no_show.`id` = (
                                        SELECT sc3.`id`
                                        FROM `schedule` sc3
                                        LEFT JOIN `schedule_status` ss3 ON sc3.`schedule_status_id` = ss3.`id`
                                        WHERE (sc3.`is_deleted` = 0 OR sc3.`is_deleted` IS NOT NULL)
                                        AND sc3.`id` = sch.`id`
                                        -- AND ss3.`description` = 'Completed'
                                        AND DATE_FORMAT(sc3.`created_at`, '%Y-%m-%d') = DATE_FORMAT(:dateFrom, '%Y-%m-%d')
                                        LIMIT 1
                                    )
                                    LEFT JOIN `schedule_status` sc1 ON sched_completed.`schedule_status_id` = sc1.`id`
                                    LEFT JOIN `schedule_status` sc2 ON sched_cancelled.`schedule_status_id` = sc2.`id`
                                    LEFT JOIN `schedule_status` sc3 ON sched_no_show.`schedule_status_id` = sc3.`id`
                                    WHERE (sch.`is_deleted` = 0 OR sch.`is_deleted` IS NULL)
                                    AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                                    AND DATE_FORMAT(sch.`created_at`, '%Y-%m-%d') = DATE_FORMAT(:dateFrom, '%Y-%m-%d')
                                    AND hf.`description` IS NOT NULL
                                    AND hf.`no_of_clinician` IS NOT NULL
                                    AND hf.`is_deleted` = 0
                                    AND ct.`daily_target_per_clinician` IS NOT NULL
                                    AND hf.`no_of_clinician` IS NOT NULL
                                    GROUP BY sch.`health_facility_id`, sch.`chart_type_id`
                                    ORDER BY hf.`description` ASC";

        $sql_healthFacility = "SELECT
                                    hf.`id` AS health_facility_id,
                                    hf.`description` AS health_facility_desc,
                                    hf.`no_of_clinician` AS no_of_clinician
                                FROM `health_facility` hf
                                WHERE hf.`no_of_clinician` IS NOT NULL
                                GROUP BY hf.`id`
                                ";

        $sql_chartType = "SELECT
                                    ct.`id` AS chart_type_id,
                                    ct.`description` AS chart_type_desc,
                                    ct.`daily_target_per_clinician` AS dailyTarget
                                FROM `chart_type` ct
                                WHERE ct.`daily_target_per_clinician` IS NOT NULL
                                GROUP BY ct.`id`
                                ";

        $query_referralCount = $this->getEntityManager()->getConnection()->prepare($sql_referralCount);
        $query_referralCount->execute($stmtParams_referral);

        $query_statusCount = $this->getEntityManager()->getConnection()->prepare($sql_statusCount);
        $query_statusCount->execute($stmtParams_status);

        $query_branchStatusCount = $this->getEntityManager()->getConnection()->prepare($sql_branchStatusCount);
        $query_branchStatusCount->execute($stmtParams_branchStatus);

        $query_healthFacility = $this->getEntityManager()->getConnection()->prepare($sql_healthFacility);
        $query_healthFacility->execute();

        $query_chartType = $this->getEntityManager()->getConnection()->prepare($sql_chartType);
        $query_chartType->execute();

        //Targets
        $result1 = $query_branchStatusCount->fetchAll();
        $result2 = $query_healthFacility->fetchAll();
        $result3 = $query_chartType->fetchAll();

        $healthFacilities = array();

        foreach($result3 as $chartTypes) {
            $healthFacility_id = null;
            $branchTarget = array();
            foreach ($result1 as $key => $branchStatus) {
                if ($chartTypes['chart_type_id'] == $branchStatus['chart_type_id']) {
                        $branchTarget[] = array(
                            'id' => $branchStatus['health_facility_id'],
                            'description' => $branchStatus['health_facility_desc'],
                            'no_of_clinician' => $branchStatus['noOfClinician'],
                            'counts' => array(
                                'completedCount' => $branchStatus['completedCount'],
                                'cancelledCount' => $branchStatus['cancelledCount'],
                                'noShowCount' => $branchStatus['noShowCount'],
                            )
                        );
                }
            }

            if(count($branchTarget)) {
                $healthFacilities[] = array(
                    'target' => $chartTypes['dailyTarget'],
                    'description' => $chartTypes['chart_type_desc'],
                    'healthFacilities' => $branchTarget
                );
            }
        }

        $result['referralCount'] = $query_referralCount->fetchAll();
        $result['statusCount'] = $query_statusCount->fetch();
        $result['branchTarget'] = $healthFacilities;

        return $result;
    }

    public function managementDashboard_monthly($params) {
        $sqlWhere = "WHERE (p.`is_deleted` = 0 OR p.`is_deleted` IS NOT NULL)";
        $sqlWhere_referral = "";
        $sqlWhere_booked = "";
        $sqlWhere_callBack = "";
        $sqlWhere_voiceMail = "";

        //STMTPARAMS
        $stmtParams_referral = array();
        $stmtParams_status = array();
        $stmtParams_branchStatus = array();

        $stmtParams_status = array();

        $stmtParams_referral['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
        $stmtParams_referral['dateTo'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_to'])));

        $stmtParams_status['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
        $stmtParams_status['dateTo'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_to'])));

        $stmtParams_branchStatus['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
        $stmtParams_branchStatus['dateTo'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_to'])));

        if($params['branch'] != null && $params['branch'] != '' && $params['branch'] != 'null') {
            $sqlWhere_referral .= " AND phf.`health_facility_id` = :branch";
            $stmtParams_referral['branch'] = $params['branch'];
        }

        if($params['company'] != null && $params['company'] != '' && $params['company'] != 'null') {
            $sqlWhere_referral .= " AND hf.`company_id` = :company";
            $stmtParams_referral['company'] = $params['company'];
        }

        if($params['provider'] != null && $params['provider'] != '' && $params['provider'] != 'null') {
            $sqlWhere_booked = " AND sc.`provider_id` = :bookedProvider";
            $sqlWhere_callBack = " AND pc2.`provider_id` = :callBackProvider";
            $sqlWhere_voiceMail = " AND pc3.`provider_id` = :voiceMailProvider";
            $stmtParams_status['bookedProvider'] = $params['provider'];
            $stmtParams_status['callBackProvider'] = $params['provider'];
            $stmtParams_status['voiceMailProvider'] = $params['provider'];
        }


        $sql_referralCount = "SELECT 
                                phf.`health_facility_id` AS branchId,
                                hf.`description` AS healthFacilityDesc,
                                COUNT(p.`id`) AS monthlyReferralCount
                              FROM `patient` p
                              LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                                      SELECT
                                          phf1.`id`
                                       FROM `patient_health_facility` phf1
                                       WHERE phf1.`end_date` IS NULL
                                       AND phf1.`patient_id` = p.`id`
                                       ORDER BY phf1.`created_at` DESC
                                       LIMIT 1
                               )
                               LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                               LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                               WHERE p.`referral_date` BETWEEN DATE_FORMAT(:dateFrom, '%Y-%m-%d') AND DATE_FORMAT(:dateTo, '%Y-%m-%d')
                               $sqlWhere_referral
                               AND p.`is_deleted` = 0
                               AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
                               AND p.`is_duplicate` = 0
                               AND hf.`description` IS NOT NULL
                               AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                               GROUP BY phf.`health_facility_id`";

        $sql_statusCount = "SELECT 
                                COUNT(DISTINCT sched_booked.`id`) AS monthlyBookedCount,
                                COUNT(DISTINCT pc_callback.`id`) AS monthlyCallBackCount,
                                COUNT(DISTINCT pc_voicemail.`id`) AS monthlyVoiceMailCount
                            FROM `patient` p
                            LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                                      SELECT
                                          phf1.`id`
                                       FROM `patient_health_facility` phf1
                                       WHERE phf1.`end_date` IS NULL
                                       AND phf1.`patient_id` = p.`id`
                                       ORDER BY phf1.`created_at` DESC
                                       LIMIT 1
                            )
                            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                            LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                            LEFT JOIN `schedule` sched_booked ON sched_booked.`id` = (
                                SELECT sc.`id`
                                FROM `schedule` sc
                                LEFT JOIN `schedule_status` ss ON sc.`schedule_status_id` = ss.`id`
                                LEFT JOIN `provider` pr ON sc.`provider_id` = pr.`id`
                                WHERE (sc.`is_deleted` = 0 OR sc.`is_deleted` IS NOT NULL)
                                AND sc.`patient_id` = p.`id`
                                AND ss.`description` = 'Booked'
                                $sqlWhere_booked
                                AND sc.`created_at` BETWEEN DATE_FORMAT(:dateFrom, '%Y-%m-%d') AND DATE_FORMAT(:dateTo, '%Y-%m-%d')
                                AND sc.`provider_id` IN (
                                   SELECT
                                      p.`id`
                                   FROM `provider_health_facility` phf
                                   LEFT JOIN `provider` p ON phf.`provider_id` = p.`id`
                                   AND phf.`is_deleted` = 0
                                )
                                LIMIT 1
                            )
                            LEFT JOIN `patient_chart` pc_callback ON pc_callback.`id` = (
                                SELECT pc2.`id`
                                FROM `patient_chart` pc2
                                LEFT JOIN `chart_type` ct2 ON pc2.`chart_type_id` = ct2.`id`
                                LEFT JOIN `internal_status` is2 ON pc2.`internal_status_id` = is2.`id`
                                LEFT JOIN `provider` pr2 ON pc2.`provider_id` = pr2.`id`
                                WHERE (pc2.`is_deleted` = 0 OR pc2.`is_deleted` IS NOT NULL)
                                AND pc2.`patient_id` = p.`id`
                                AND is2.`description` = 'Call Back'
                                $sqlWhere_callBack
                                AND DATE_FORMAT(pc2.`updated_at`, '%Y-%m-%d') BETWEEN DATE_FORMAT(:dateFrom, '%Y-%m-%d') AND DATE_FORMAT(:dateTo, '%Y-%m-%d')
                                LIMIT 1
                            )
                            LEFT JOIN `patient_chart` pc_voicemail ON pc_voicemail.`id` = (
                                SELECT pc3.`id`
                                FROM `patient_chart` pc3
                                LEFT JOIN `chart_type` ct3 ON pc3.`chart_type_id` = ct3.`id`
                                LEFT JOIN `internal_status` is3 ON pc3.`internal_status_id` = is3.`id`
                                LEFT JOIN `provider` pr3 ON pc3.`provider_id` = pr3.`id`
                                WHERE (pc3.`is_deleted` = 0 OR pc3.`is_deleted` IS NOT NULL)
                                AND pc3.`patient_id` = p.`id`
                                $sqlWhere_voiceMail
                                AND is3.`description` = 'Voice Mail'
                                AND DATE_FORMAT(pc3.`updated_at`, '%Y-%m-%d') BETWEEN DATE_FORMAT(:dateFrom, '%Y-%m-%d') AND DATE_FORMAT(:dateTo, '%Y-%m-%d')
                                LIMIT 1
                            )
                            $sqlWhere";

        $sql_branchStatusCount = "SELECT
                                      hf.`id` AS health_facility_id,
                                      hf.`description` AS health_facility_desc,
                                      ct.`id` AS chart_type_id,
                                      hf.`no_of_clinician` AS noOfClinician,
                                      ct.`description` AS chartType,
                                      ct.`daily_target_per_clinician` AS dailyTarget,
                                      SUM(IF(sc1.`description` = 'Completed', 1, 0)) AS completedCount,
                                      SUM(IF(sc2.`description` = 'Cancelled', 1, 0)) AS cancelledCount,
                                      SUM(IF(sc3.`description` = 'No Show', 1, 0)) AS noShowCount
                                    FROM `schedule` sch
                                    LEFT JOIN `chart_type` ct ON sch.`chart_type_id` = ct.`id`
                                    LEFT JOIN `health_facility` hf ON sch.`health_facility_id` = hf.`id`
                                    LEFT JOIN `schedule` sched_completed ON sched_completed.`id` = (
                                        SELECT sc1.`id`
                                        FROM `schedule` sc1
                                        LEFT JOIN `schedule_status` ss1 ON sc1.`schedule_status_id` = ss1.`id`
                                        WHERE (sc1.`is_deleted` = 0 OR sc1.`is_deleted` IS NOT NULL)
                                        AND sc1.`id` = sch.`id`
                                        -- AND ss1.`description` = 'Completed'
                                        AND DATE_FORMAT(sc1.`created_at`, '%Y-%m-%d') BETWEEN DATE_FORMAT(:dateFrom, '%Y-%m-%d') AND DATE_FORMAT(:dateTo, '%Y-%m-%d')
                                        LIMIT 1
                                    )
                                    LEFT JOIN `schedule` sched_cancelled ON sched_cancelled.`id` = (
                                        SELECT sc2.`id`
                                        FROM `schedule` sc2
                                        LEFT JOIN `schedule_status` ss2 ON sc2.`schedule_status_id` = ss2.`id`
                                        WHERE (sc2.`is_deleted` = 0 OR sc2.`is_deleted` IS NOT NULL)
                                        AND sc2.`id` = sch.`id`
                                        -- AND ss2.`description` = 'Completed'
                                        AND DATE_FORMAT(sc2.`created_at`, '%Y-%m-%d') BETWEEN DATE_FORMAT(:dateFrom, '%Y-%m-%d') AND DATE_FORMAT(:dateTo, '%Y-%m-%d')
                                        LIMIT 1
                                    )
                                    LEFT JOIN `schedule` sched_no_show ON sched_no_show.`id` = (
                                        SELECT sc3.`id`
                                        FROM `schedule` sc3
                                        LEFT JOIN `schedule_status` ss3 ON sc3.`schedule_status_id` = ss3.`id`
                                        WHERE (sc3.`is_deleted` = 0 OR sc3.`is_deleted` IS NOT NULL)
                                        AND sc3.`id` = sch.`id`
                                        -- AND ss3.`description` = 'Completed'
                                        AND DATE_FORMAT(sc3.`created_at`, '%Y-%m-%d') BETWEEN DATE_FORMAT(:dateFrom, '%Y-%m-%d') AND DATE_FORMAT(:dateTo, '%Y-%m-%d')
                                        LIMIT 1
                                    )
                                    LEFT JOIN `schedule_status` sc1 ON sched_completed.`schedule_status_id` = sc1.`id`
                                    LEFT JOIN `schedule_status` sc2 ON sched_cancelled.`schedule_status_id` = sc2.`id`
                                    LEFT JOIN `schedule_status` sc3 ON sched_no_show.`schedule_status_id` = sc3.`id`
                                    WHERE (sch.`is_deleted` = 0 OR sch.`is_deleted` IS NULL)
                                    AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                                    AND DATE_FORMAT(sch.`created_at`, '%Y-%m-%d') BETWEEN DATE_FORMAT(:dateFrom, '%Y-%m-%d') AND DATE_FORMAT(:dateTo, '%Y-%m-%d')
                                    AND hf.`description` IS NOT NULL
                                    AND hf.`no_of_clinician` IS NOT NULL
                                    AND ct.`daily_target_per_clinician` IS NOT NULL
                                    AND hf.`no_of_clinician` IS NOT NULL
                                    AND hf.`is_deleted` = 0
                                    GROUP BY sch.`health_facility_id`, sch.`chart_type_id`
                                    ORDER BY hf.`description` ASC";

        $sql_healthFacility = "SELECT
                                    hf.`id` AS health_facility_id,
                                    hf.`description` AS health_facility_desc,
                                    hf.`no_of_clinician` AS no_of_clinician
                                FROM `health_facility` hf
                                WHERE hf.`no_of_clinician` IS NOT NULL
                                GROUP BY hf.`id`
                                ";

        $sql_chartType = "SELECT
                                    ct.`id` AS chart_type_id,
                                    ct.`description` AS chart_type_desc,
                                    ct.`daily_target_per_clinician` AS dailyTarget
                                FROM `chart_type` ct
                                WHERE ct.`daily_target_per_clinician` IS NOT NULL
                                GROUP BY ct.`id`
                                ";

        $conn = $this->getEntityManager()->getConnection();

        $query_referralCount = $conn->prepare($sql_referralCount);
        $query_referralCount->execute($stmtParams_referral);

        $query_statusCount = $conn->prepare($sql_statusCount);
        $query_statusCount->execute($stmtParams_status);

        $query_branchStatusCount = $conn->prepare($sql_branchStatusCount);
        $query_branchStatusCount->execute($stmtParams_branchStatus);

        $query_healthFacility = $conn->prepare($sql_healthFacility);
        $query_healthFacility->execute();

        $query_chartType = $conn->prepare($sql_chartType);
        $query_chartType->execute();

        //Targets
        $result1 = $query_branchStatusCount->fetchAll();
        $result2 = $query_healthFacility->fetchAll();
        $result3 = $query_chartType->fetchAll();

        $healthFacilities = array();

        foreach($result3 as $chartTypes) {
            $healthFacility_id = null;
            $branchTarget = array();
            foreach ($result1 as $key => $branchStatus) {
                if ($chartTypes['chart_type_id'] == $branchStatus['chart_type_id']) {
                    $branchTarget[] = array(
                        'id' => $branchStatus['health_facility_id'],
                        'description' => $branchStatus['health_facility_desc'],
                        'no_of_clinician' => $branchStatus['noOfClinician'],
                        'counts' => array(
                            'completedCount' => $branchStatus['completedCount'],
                            'cancelledCount' => $branchStatus['cancelledCount'],
                            'noShowCount' => $branchStatus['noShowCount'],
                        )
                    );
                }
            }
            $healthFacilities[] = array(
                'target' => $chartTypes['dailyTarget'],
                'description' => $chartTypes['chart_type_desc'],
                'healthFacilities' => $branchTarget
            );
        }

        $result['referralCount'] = $query_referralCount->fetchAll();
        $result['statusCount'] = $query_statusCount->fetch();
        $result['branchTarget'] = $healthFacilities;

        return $result;
    }

    public function managementDashboard_cycle($params) {
        $sqlWhere = "WHERE (ct.`is_deleted` = 0 OR ct.`is_deleted` IS NOT NULL)";
        $sqlWhere2 = " AND p.`referral_date` BETWEEN :dateFrom AND :dateTo";
        $sqlWhereReferral = " AND p.`referral_date` BETWEEN :dateFrom AND :dateTo";

        //STMTPARAMS
        $stmtParams_cycle = array();
        $stmtParams_cycle['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
        $stmtParams_cycle['dateTo'] = date('Y-m-d 23:59:59', strtotime(str_replace('-',  '/', $params['date_to'])));

        $stmtParams_referral['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
        $stmtParams_referral['dateTo'] = date('Y-m-d 23:59:59', strtotime(str_replace('-',  '/', $params['date_to'])));

        if($params['branch'] != null && $params['branch'] != '' && $params['branch'] != 'null') {
            $sqlWhere2 .= " AND phf.`health_facility_id` = :branch";
            $stmtParams_cycle['branch'] = $params['branch'];
        }

        if($params['company'] != null && $params['company'] != '' && $params['company'] != 'null') {
            $sqlWhere2 .= " AND hf.`company_id` = :company";
            $stmtParams_cycle['company'] = $params['company'];
        }

        if($params['provider'] != null && $params['provider'] != '' && $params['provider'] != 'null') {
            $sqlWhere2 .= " AND pch.`provider_id` = :provider";
            $stmtParams_cycle['provider'] = $params['provider'];
        }

        $sql_referralCount = "SELECT 
                                COUNT(DISTINCT p.`id`) AS totalReferral
                              FROM `patient` p
                              LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                                SELECT phf1.`id`
                                FROM `patient_health_facility` phf1
                                WHERE phf.`is_deleted` = 0
                                AND phf1.`patient_id` = p.`id`
                                AND phf1.`end_date` IS NULL
                                ORDER BY phf1.`start_date` DESC
                                LIMIT 1
                              )
                              LEFT JOIN `provider` pr ON p.`provider_id` = pr.`id`
                              LEFT JOIN `health_facility` hf ON hf.`id` = phf.`health_facility_id`
                              LEFT JOIN `company` co ON co.`id` = hf.`company_id`
                              WHERE p.`is_deleted` = 0
                              $sqlWhereReferral
                              AND p.`is_deleted` = 0
                              AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
                              AND p.`is_duplicate` = 0
                              AND hf.`description` IS NOT NULL
                              AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')";

        $sql = "SELECT 
                   ct.`description` AS chartType,
                  (SELECT
                    COUNT(DISTINCT p.`id`)
                   FROM `patient` p
                   LEFT JOIN `patient_chart` pc ON pc.`id` = (
                    SELECT pcc.`id`
                    FROM `patient_chart` pcc
                    LEFT JOIN `chart_status` cs ON pcc.`chart_status_id` = cs.`id`
                    WHERE pcc.`patient_id` = p.`id`
                    AND (pcc.is_deleted = 0 OR pcc.`is_deleted` IS NULL)
                    AND pcc.`chart_type_id` = ct.`id`
                    ORDER BY pcc.`chart_date` DESC, cs.`ordinal` DESC
                    LIMIT 1
                   )
                   LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                      SELECT
                          phf1.`id`
                       FROM `patient_health_facility` phf1
                       WHERE phf1.`end_date` IS NULL
                       AND phf1.`patient_id` = p.`id`
                       ORDER BY phf1.`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 `provider` pr ON pc.`provider_id` = pr.`id`
                   LEFT JOIN `patient_chart` pch ON pch.`id` = (
                          SELECT 
                            pcpc.`id` 
                          FROM `patient_chart` pcpc
                          LEFT JOIN `chart_type` ctpc ON pcpc.`chart_type_id` = ctpc.`id`
                          WHERE ctpc.`description` IN(
                            'HSAT',
                            'HSAT Curbside',
                            'HSAT Curbside (Repeat)',
                            'HSAT (Mobile)',
                            'HSAT Mobile (Repeat)'
                          )
                          AND pcpc.`patient_id` = p.`id`
                          AND pcpc.`is_deleted` = 0
                          ORDER BY pcpc.`chart_date` DESC
                          LIMIT 1
                        )
                   LEFT JOIN `provider` pch_prov ON pch.`provider_id` = pch_prov.`id`
                   WHERE IF(
                      pc.`id` IS NULL,
                      DATE_ADD(p.`referral_date`, INTERVAL ct.`expected_no_of_days` DAY) < :now,
                      DATEDIFF(pc.`chart_date`, p.`referral_date`) > ct.`expected_no_of_days`
                   )
                   $sqlWhere2
                   AND p.`is_duplicate` = 0
                   AND (p.`is_deleted` = 0 OR p.`is_deleted` IS NULL)
                   AND p.`is_duplicate` = 0
                   AND p.`is_test_patient` = 0
                   AND p.`full_name` IS NOT NULL
                   AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                   AND hf.`description` IS NOT NULL    
                   AND p.`full_name` IS NOT NULL
                   AND p.`referral_date` IS NOT NULL
                   AND p.`referral_date` != '0000-00-00 00:00:00'
                  ) AS cnt
                FROM `chart_type` ct
                $sqlWhere
                AND ct.`rpt_prod_ordinal` IS NOT NULL
                AND ct.`rpt_prod_ordinal` <= '6'
                ORDER BY ct.`rpt_prod_ordinal` ASC
        ";

        $sql_conv1 = "
                SELECT
                    COUNT(p.`id`) AS cnt1
                FROM `patient` p
                LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                    SELECT
                    phf1.`id`
                   FROM `patient_health_facility` phf1
                   WHERE phf1.`end_date` IS NULL
                   AND phf1.`patient_id` = p.`id`
                   ORDER BY phf1.`created_at` DESC
                   LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                WHERE (p.`is_deleted` = 0 OR p.`is_deleted` IS NOT NULL)
                AND p.`referral_date` BETWEEN :dateFrom AND :dateTo
                AND p.`id` NOT IN(
                                    SELECT pc.`patient_id`
                                    FROM `patient_chart` pc
                                    WHERE pc.`is_deleted` = 0
                                    AND pc.`patient_id` = p.`id`
                                  )
                ORDER BY p.`referral_date` DESC
        ";

        $sql_conv2 = "
                SELECT
                    COUNT(p.`id`) AS cnt2
                FROM `patient` p
                LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                    SELECT
                    phf1.`id`
                   FROM `patient_health_facility` phf1
                   WHERE phf1.`end_date` IS NULL
                   AND phf1.`patient_id` = p.`id`
                   ORDER BY phf1.`created_at` DESC
                   LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                WHERE (p.`is_deleted` = 0 OR p.`is_deleted` IS NOT NULL)
                AND p.`referral_date` BETWEEN :dateFrom AND :dateTo
                AND p.`id` IN(
                                    SELECT pc.`patient_id`
                                    FROM `patient_chart` pc
                                    LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                                    LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                                    WHERE pc.`is_deleted` = 0
                                    AND pc.`patient_id` = p.`id`
                                    AND ct.`description` IN(
                                        'HSAT',
                                        'HSAT Curbside',
                                        'HSAT Curbside (Repeat)',
                                        'HSAT (Mobile)',
                                        'HSAT Mobile (Repeat)'
                                    )
                                    AND pc.`patient_id` NOT IN(
                                        SELECT pc1.`patient_id`
                                        FROM `patient_chart` pc1
                                        LEFT JOIN `chart_type` ct1 ON pc1.`chart_type_id` = ct1.`id`
                                        LEFT JOIN `chart_status` cs1 ON pc1.`chart_status_id` = cs1.`id`
                                        WHERE pc1.`is_deleted` = 0
                                        AND pc1.`patient_id` = pc.`patient_id`
                                        AND ct1.`description` = 'HSAT Results Interpretation'
                                        AND ct1.`description` = 'HSAT Results Interpretation - Telehealth'
                                    )
                                )
                ORDER BY p.`referral_date` DESC
        ";

        $sql_conv3 = "
                SELECT
                   COUNT(p.`id`) AS cnt3
                FROM `patient` p
                LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                    SELECT
                    phf1.`id`
                   FROM `patient_health_facility` phf1
                   WHERE phf1.`end_date` IS NULL
                   AND phf1.`patient_id` = p.`id`
                   ORDER BY phf1.`created_at` DESC
                   LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                WHERE (p.`is_deleted` = 0 OR p.`is_deleted` IS NOT NULL)
                AND p.`referral_date` BETWEEN :dateFrom AND :dateTo
                AND p.`id` IN(
                                    SELECT pc.`patient_id`
                                    FROM `patient_chart` pc
                                    LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                                    LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                                    WHERE pc.`is_deleted` = 0
                                    AND pc.`patient_id` = p.`id`
                                    AND ct.`description` = 'HSAT Results Interpretation'
                                    AND ct.`description` = 'HSAT Results Interpretation - Telehealth'
                                    AND pc.`patient_id` NOT IN(
                                        SELECT pc1.`patient_id`
                                        FROM `patient_chart` pc1
                                        LEFT JOIN `chart_type` ct1 ON pc1.`chart_type_id` = ct1.`id`
                                        LEFT JOIN `chart_status` cs1 ON pc1.`chart_status_id` = cs1.`id`
                                        WHERE pc1.`is_deleted` = 0
                                        AND pc1.`patient_id` = pc.`patient_id`
                                        AND ct1.`description` = 'CPAP Setup'
                                        AND ct1.`description` = 'CPAP Setup - Telehealth'
                                    )
                                )
                ORDER BY p.`referral_date` DESC
        ";

        $sql_conv4 = "
                SELECT
                    COUNT(p.`id`) AS cnt4
                FROM `patient` p
                LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                    SELECT
                    phf1.`id`
                   FROM `patient_health_facility` phf1
                   WHERE phf1.`end_date` IS NULL
                   AND phf1.`patient_id` = p.`id`
                   ORDER BY phf1.`created_at` DESC
                   LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                WHERE (p.`is_deleted` = 0 OR p.`is_deleted` IS NOT NULL)
                AND p.`referral_date` BETWEEN :dateFrom AND :dateTo
                AND p.`id` IN(
                                    SELECT pc.`patient_id`
                                    FROM `patient_chart` pc
                                    LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                                    LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                                    WHERE pc.`is_deleted` = 0
                                    AND pc.`patient_id` = p.`id`
                                    AND ct.`description` = 'CPAP Setup'
                                    AND pc.`patient_id` NOT IN(
                                        SELECT pb.`patient_id`
                                        FROM `patient_billing` pb
                                        WHERE pb.`is_deleted` = 0
                                        AND (pb.`billing_status` != 'Void' OR pb.`billing_status` = 'Pending Payment')
                                        AND pb.`patient_id` = pc.`patient_id`
                                    )
                                )
                ORDER BY p.`referral_date` DESC
        ";

        $conn = $this->getEntityManager()->getConnection();

        $query1 = $conn->prepare($sql_referralCount);
        $query1->execute($stmtParams_referral);

        $query_conv1 = $conn->prepare($sql_conv1);
        $query_conv1->execute($stmtParams_referral);

        $query_conv2 = $conn->prepare($sql_conv2);
        $query_conv2->execute($stmtParams_referral);

        $query_conv3 = $conn->prepare($sql_conv3);
        $query_conv3->execute($stmtParams_referral);

        $query_conv4 = $conn->prepare($sql_conv4);
        $query_conv4->execute($stmtParams_referral);

        $stmtParams_cycle['now'] = date('Y-m-d 23:59:59');
        $query2 = $conn->prepare($sql);
        $query2->execute($stmtParams_cycle);

        $result['referral'] = $query1->fetch();
        $result['encounter_cycle'] = $query2->fetchAll();
        $result['encounter_cycle_total_cnt'] = 0;
        $result['conv1'] = $query_conv1->fetchAll();
        $result['conv2'] = $query_conv2->fetchAll();
        $result['conv3'] = $query_conv3->fetchAll();
        $result['conv4'] = $query_conv4->fetchAll();
        foreach($result['encounter_cycle'] as $item){
            $result['encounter_cycle_total_cnt'] += $item['cnt'];
        }

        return $result;
    }

    public function managementDashboard_encounters($params) {
        $stmtParams = array();
        $stmtParams2 = array();
        $sqlWhere = "WHERE pc.`is_deleted` = 0";
        $sqlWhere .= " AND pc.`chart_date` BETWEEN :dateFrom AND :dateTo";
        $sqlWhere2 = " AND p.`referral_date` BETWEEN DATE_FORMAT(:dateFrom, '%Y-%m-%d') AND DATE_FORMAT(:dateTo, '%Y-%m-%d')";

        if($params['company'] != null && $params['company'] != '' && $params['company'] != 'null') {
            $sqlWhere .= " AND hf.`company_id` = :companyDesc";
            $sqlWhere2 .= " AND hf.`company_id` = :companyDesc";
            $stmtParams['companyDesc'] = $params['company'];
            $stmtParams2['companyDesc'] = $params['company'];
        }

        if($params['branch'] != null && $params['branch'] != '' && $params['branch'] != 'null') {
            $sqlWhere .= " AND pc.`health_facility_id` = :healthFacilityDesc";
            $sqlWhere2 .= " AND phf.`health_facility_id` = :healthFacilityDesc";
            $stmtParams['healthFacilityDesc'] = $params['branch'];
            $stmtParams2['healthFacilityDesc'] = $params['branch'];
        }

        if($params['provider'] != null && $params['provider'] != '' && $params['provider'] != 'null') {
            $sqlWhere .= " AND pc.`provider_id` = :provider";
//            $sqlWhere2 .= " AND p.`provider_id` = :provider";
            $stmtParams['provider'] = $params['provider'];
//            $stmtParams2['provider'] = $params['provider'];
        }

        $stmtParams['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
        $stmtParams['dateTo'] = date('Y-m-d 23:59:59', strtotime(str_replace('-',  '/', $params['date_to'])));

        $stmtParams2['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
        $stmtParams2['dateTo'] = date('Y-m-d 23:59:59', strtotime(str_replace('-',  '/', $params['date_to'])));

        $sql_referralCount = "SELECT 
                                COUNT(DISTINCT p.`id`) AS totalReferral
                              FROM `patient` p
                              LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                                SELECT phf1.`id`
                                FROM `patient_health_facility` phf1
                                WHERE phf.`is_deleted` = 0
                                AND phf1.`patient_id` = p.`id`
                                AND phf1.`end_date` IS NULL
                                ORDER BY phf1.`start_date` DESC
                                LIMIT 1
                              )
                              LEFT JOIN `patient_chart` pc ON pc.`id` = (
                                SELECT pc1.`id`
                                FROM `patient_chart` pc1
                                LEFT JOIN `chart_type` ct1 ON pc1.`chart_type_id` = ct1.`id`
                                LEFT JOIN `chart_status` cs1 ON pc1.`chart_status_id` = cs1.`id`
                                WHERE pc1.`is_deleted` = 0
                                AND pc1.`patient_id` = p.`id`
                                AND ct1.`description` IN(
                                  'HSAT',
                                  'HSAT Curbside',
                                  'HSAT Curbside (Repeat)',
                                  'HSAT (Mobile)',
                                  'HSAT Mobile (Repeat)'
                                )
                                ORDER BY pc1.`chart_date` DESC
                                LIMIT 1
                              )
                              LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
                              LEFT JOIN `health_facility` hf ON hf.`id` = phf.`health_facility_id`
                              LEFT JOIN `company` co ON co.`id` = hf.`company_id`
                              WHERE p.`is_deleted` = 0
                              $sqlWhere2
                              AND p.`is_deleted` = 0
                              AND p.`is_duplicate` = 0
                              AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
                              AND p.`full_name` IS NOT NULL
                              AND p.`referral_date` IS NOT NULL
                              AND p.`referral_date` != '0000-00-00 00:00:00'
                              AND hf.`description` IS NOT NULL
                              AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')";

        $sql1 = "SELECT 
                    ct.`id` AS chart_type_id,
                    ct.`description` AS chartType
                FROM `chart_type` ct
                WHERE ct.`is_deleted` = 0
                ORDER BY FIELD(ct.`description`, 
                'CPAP End Eval', 
                'CPAP Eval', 
                'CPAP Setup Sold', 
                'CPAP Setup - Telehealth', 
                'CPAP Setup', 
                'HSAT Results Interpretation - Telehealth', 
                'HSAT Results Interpretation', 
                'HSAT Mobile (Repeat)', 
                'HSAT (Mobile)', 
                'HSAT Curbside (Repeat)', 
                'HSAT Curbside', 
                'HSAT', 
                'Intro Phone Call'
                ) DESC
                ";

        $sql2 = "SELECT 
                    pc.`chart_type_id`,
                    pc.chart_status_id,
                    cs.`description` AS chart_status_description,
                    COUNT(pc.`id`) AS cnt
                FROM `patient_chart` pc
                LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                LEFT JOIN `health_facility` hf ON hf.`id` = pc.`health_facility_id`
                LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
                $sqlWhere
                AND pc.`chart_status_id` IS NOT NULL
                AND p.`full_name` IS NOT NULL
                GROUP BY pc.`chart_type_id`, cs.`description`
                ORDER BY FIELD(cs.`description`, 'Booked', 'Completed') DESC
                ";

        $conn = $this->getEntityManager()->getConnection();

        $query1 = $conn->prepare($sql1);
        $query2 = $conn->prepare($sql2);
        $query3 = $conn->prepare($sql_referralCount);

        $query1->execute();
        $query2->execute($stmtParams);
        $query3->execute($stmtParams2);

        $result1 = $query1->fetchAll();
        $result2 = $query2->fetchAll();
        $result3 = $query3->fetchAll();

        $totalReferral = $result3[0]['totalReferral'];
        $patientChartCount = array();
        $hsatCnt = 0;
        $hriCnt = 0;
        $cpapSetupCnt = 0;
        $cpapSetupSoldCnt = 0;
        $cpapEvalCnt = 0;
        $cpapEndEvalCnt = 0;

        foreach ($result1 as $key1 => $chartTypes) {
            $chartTypeCount = array();
            $percentage = 0;

            foreach ($result2 as $key2 => $patientCharts) {
                if($chartTypes['chart_type_id'] == $patientCharts['chart_type_id']) {

                    if ($totalReferral > 0) {
                        if ($patientCharts['chart_status_description'] == 'Completed') {

//                            if ($chartTypes['chartType'] == 'HSAT') {
//                                $hsatCnt = $patientCharts['cnt'];
//                                $percentage = $patientCharts['cnt'] / $totalReferral * 100;
//                            }
//
//                            if ($chartTypes['chartType'] == 'HSAT Results Interpretation') {
//                                $hriCnt = $patientCharts['cnt'];
//                                $percentage = $patientCharts['cnt'] / $hsatCnt * 100;
//                            }
//
//                            if ($chartTypes['chartType'] == 'CPAP Setup') {
//                                $cpapSetupCnt = $patientCharts['cnt'];
//                                $percentage = $patientCharts['cnt'] / $hriCnt * 100;
//                            }
//
//                            if ($chartTypes['chartType'] == 'CPAP Setup Sold') {
//                                $cpapSetupSoldCnt = $patientCharts['cnt'];
//                                $percentage = $patientCharts['cnt'] / $cpapSetupCnt * 100;
//                            }

//                            if ($chartTypes['chartType'] == 'CPAP Eval') {
//                                $cpapEvalCnt = $patientCharts['cnt'];
//                                $percentage = $patientCharts['cnt'] / $cpapSetupSoldCnt * 100;
//                            }
//
//                            if ($chartTypes['chartType'] == 'CPAP End Eval') {
//                                $cpapEndEvalCnt = $patientCharts['cnt'];
//                                $percentage = $patientCharts['cnt'] / $cpapEvalCnt * 100;
//                            }
                        }
                    }


                    $chartTypeCount[] = array(
                        'chart_type_id' => $patientCharts['chart_type_id'],
                        'patient_chart_count' => array(
                            'chart_status_id' => $patientCharts['chart_status_id'],
                            'chart_status' => $patientCharts['chart_status_description'],
                            'chart_status_count' => $patientCharts['cnt'],
                        )
                    );
                }
            }

            if($chartTypes['chartType'] == 'HSAT' || $chartTypes['chartType'] == 'HSAT Curbside' || $chartTypes['chartType'] == 'HSAT Curbside (Repeat)' || $chartTypes['chartType'] == 'HSAT (Mobile)' || $chartTypes['chartType'] == 'HSAT Mobile (Repeat)' || $chartTypes['chartType'] == 'HSAT Results Interpretation' || $chartTypes['chartType'] == 'HSAT Results Interpretation - Telehealth' || $chartTypes['chartType'] == 'CPAP Setup' || $chartTypes['chartType'] == 'CPAP Set Up (Mobile)' || $chartTypes['chartType'] == '	CPAP Setup - Telehealth' || $chartTypes['chartType'] == 'CPAP Setup Sold') {
                if (count($chartTypeCount)) {
                    $patientChartCount[] = array(
                        'referrals' => $result3,
                        'description' => $chartTypes['chartType'],
                        'chart_types' => $chartTypeCount,
                        'percentage' => $percentage

                    );
                }
            } else {
                if (count($chartTypeCount)) {
                    $patientChartCount[] = array(
                        'referrals' => $result3,
                        'description' => $chartTypes['chartType'],
                        'chart_types' => $chartTypeCount,
                    );
                }
            }

        }

        return $patientChartCount;
    }

    public function managementDashboard_conversion($params) {
        $stmtParams = array();
        $stmtParams2 = array();
        $stmtParams3 = array();
        $sqlWhere = "WHERE pc.`is_deleted` = 0";
        $sqlWhere .= " AND p.`referral_date` BETWEEN :dateFrom AND :dateTo";
        $sqlWhere2 = " AND p.`referral_date` BETWEEN DATE_FORMAT(:dateFrom, '%Y-%m-%d') AND DATE_FORMAT(:dateTo, '%Y-%m-%d')";
        $sqlWhere3 = " AND p.`referral_date` BETWEEN DATE_FORMAT(:dateFrom, '%Y-%m-%d') AND DATE_FORMAT(:dateTo, '%Y-%m-%d')";

        if($params['company'] != null && $params['company'] != '' && $params['company'] != 'null') {
            $sqlWhere .= " AND hf.`company_id` = :companyDesc";
            $sqlWhere2 .= " AND hf.`company_id` = :companyDesc";
            $sqlWhere3 .= " AND hf.`company_id` = :companyDesc";
            $stmtParams['companyDesc'] = $params['company'];
            $stmtParams2['companyDesc'] = $params['company'];
            $stmtParams3['companyDesc'] = $params['company'];
        }

        if($params['branch'] != null && $params['branch'] != '' && $params['branch'] != 'null') {
            $sqlWhere .= " AND phf.`health_facility_id` = :healthFacilityDesc";
            $sqlWhere2 .= " AND phf.`health_facility_id` = :healthFacilityDesc";
            $sqlWhere3 .= " AND phf.`health_facility_id` = :healthFacilityDesc";
            $stmtParams['healthFacilityDesc'] = $params['branch'];
            $stmtParams2['healthFacilityDesc'] = $params['branch'];
            $stmtParams3['healthFacilityDesc'] = $params['branch'];
        }

        if($params['provider'] != null && $params['provider'] != '' && $params['provider'] != 'null') {
            $sqlWhere .= " AND pch.`provider_id` = :provider";
            $sqlWhere2 .= " AND pc.`provider_id` = :provider";
            $sqlWhere3 .= " AND pch.`provider_id` = :provider";
            $stmtParams['provider'] = $params['provider'];
            $stmtParams2['provider'] = $params['provider'];
            $stmtParams3['provider'] = $params['provider'];
        }

        $stmtParams['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
        $stmtParams['dateTo'] = date('Y-m-d 23:59:59', strtotime(str_replace('-',  '/', $params['date_to'])));

        $stmtParams2['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
        $stmtParams2['dateTo'] = date('Y-m-d 23:59:59', strtotime(str_replace('-',  '/', $params['date_to'])));

        $stmtParams3['dateFrom'] = date('Y-m-d 00:00:00', strtotime(str_replace('-',  '/', $params['date_from'])));
        $stmtParams3['dateTo'] = date('Y-m-d 23:59:59', strtotime(str_replace('-',  '/', $params['date_to'])));

        $sql_referralCount = "SELECT 
                                COUNT(DISTINCT p.`id`) AS totalReferral
                              FROM `patient` p
                              LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                                SELECT phf1.`id`
                                FROM `patient_health_facility` phf1
                                WHERE phf.`is_deleted` = 0
                                AND phf1.`patient_id` = p.`id`
                                AND phf1.`end_date` IS NULL
                                ORDER BY phf1.`start_date` DESC
                                LIMIT 1
                              )
                              LEFT JOIN `patient_chart` pc ON pc.`id` = (
                                SELECT pc1.`id`
                                FROM `patient_chart` pc1
                                LEFT JOIN `chart_type` ct1 ON pc1.`chart_type_id` = ct1.`id`
                                LEFT JOIN `chart_status` cs1 ON pc1.`chart_status_id` = cs1.`id`
                                WHERE pc1.`is_deleted` = 0
                                AND pc1.`patient_id` = p.`id`
                                AND ct1.`description` IN(
                                  'HSAT',
                                  'HSAT Curbside',
                                  'HSAT Curbside (Repeat)',
                                  'HSAT (Mobile)',
                                  'HSAT Mobile (Repeat)'
                                )
                                ORDER BY pc1.`chart_date` DESC
                                LIMIT 1
                              )
                              LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
                              LEFT JOIN `health_facility` hf ON hf.`id` = phf.`health_facility_id`
                              LEFT JOIN `company` co ON co.`id` = hf.`company_id`
                              WHERE p.`is_deleted` = 0
                              $sqlWhere2
                              AND p.`is_deleted` = 0
                              AND p.`is_duplicate` = 0
                              AND (p.`is_not_count_as_referral` = 0 OR p.`is_not_count_as_referral` IS NULL)
                              AND p.`full_name` IS NOT NULL
                              AND p.`referral_date` IS NOT NULL
                              AND p.`referral_date` != '0000-00-00 00:00:00'
                              AND hf.`description` IS NOT NULL
                              AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')";

        $sql1 = "SELECT 
                    ct.`id` AS chart_type_id,
                    ct.`description` AS chartType
                FROM `chart_type` ct
                WHERE ct.`is_deleted` = 0
                ORDER BY FIELD(ct.`description`, 'CPAP End Eval', 'CPAP Eval', 'CPAP Setup Sold', 'CPAP Setup', 'HSAT Results Interpretation', 'HSAT (Mobile)', 'HSAT', 'Intro Phone Call') DESC
                ";

        $sql2 = "
                SELECT
                    pc.`chart_type_id`,
                    pc.`chart_status_id`,
                    cs.`description` AS chart_status_description,
                    COUNT(pc.`patient_id`) AS cnt
                FROM (
                  SELECT pc4.*
                  FROM `patient_chart` pc4
                  WHERE pc4.`is_deleted` = 0
                  GROUP BY pc4.`patient_id`, pc4.`chart_type_id`, pc4.`chart_status_id`, pc4.`chart_date`
                ) pc
                LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
                LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                          SELECT
                              phf1.`id`
                          FROM `patient_health_facility` phf1
                          WHERE phf1.`end_date` IS NULL
                          AND phf1.`patient_id` = p.`id`
                          ORDER BY phf1.`created_at` DESC
                          LIMIT 1
                )
                LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
                LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
                LEFT JOIN `patient_chart` pc2 ON pc2.`id` = (
                    SELECT pc3.`id`
                    FROM `patient_chart` pc3
                    LEFT JOIN `chart_status` cs2 ON pc3.`chart_status_id` = cs2.`id`
                    WHERE pc3.patient_id = pc.`patient_id`
                    AND pc3.`is_deleted` = 0
                    AND pc3.`chart_status_id` IS NOT NULL
                    AND pc3.`chart_type_id` = pc.`chart_type_id`
                    ORDER BY pc3.`chart_date` DESC, cs2.`ordinal` DESC
                    LIMIT 1
                )
                LEFT JOIN `patient_chart` pch ON pch.`id` = (
                          SELECT 
                            pcpc.`id` 
                          FROM `patient_chart` pcpc
                          LEFT JOIN `chart_type` ctpc ON pcpc.`chart_type_id` = ctpc.`id`
                          LEFT JOIN `chart_status` cspc ON pcpc.`chart_status_id` = cspc.`id`
                          WHERE ctpc.`description` IN(
                            'HSAT',
                            'HSAT Curbside',
                            'HSAT Curbside (Repeat)',
                            'HSAT (Mobile)',
                            'HSAT Mobile (Repeat)'
                          )
                          AND cspc.`description` = 'Completed'
                          AND pcpc.`patient_id` = p.`id`
                          AND pcpc.`is_deleted` = 0
                          ORDER BY pcpc.`chart_date` DESC
                          LIMIT 1
                        )
                LEFT JOIN `provider` pch_prov ON pch.`provider_id` = pch_prov.`id`
                LEFT JOIN `chart_status` cs3 ON cs3.`id` = pc2.`chart_status_id`
                $sqlWhere
                AND p.`full_name` IS NOT NULL
                AND p.`is_duplicate` = 0
                AND p.`full_name` IS NOT NULL
                AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                AND hf.`description` IS NOT NULL
                AND p.`referral_date` IS NOT NULL
                AND p.`referral_date` != '0000-00-00 00:00:00'
                AND pc.`chart_date` >= pc2.`chart_date` 
                AND cs.ordinal >= cs3.`ordinal`
                AND p.`is_test_patient` = 0
                AND p.`is_deleted` = 0
                GROUP BY pc.`chart_type_id`, cs.`description`
                ";

        $sql_invoices = "SELECT 
                          SUM(IF(pb.`billing_date` IS NOT NULL, 1, 0)) AS invoicesCount
                        FROM `patient` p
                        LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                          SELECT
                              phf1.`id`
                           FROM `patient_health_facility` phf1
                           WHERE phf1.`end_date` IS NULL
                           AND phf1.`patient_id` = p.`id`
                           ORDER BY phf1.`created_at` DESC
                           LIMIT 1
                        )
                        LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                        LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                        LEFT JOIN `patient_chart` pch ON pch.`id` = (
                          SELECT 
                            pct1.`id` 
                          FROM `patient_chart` pct1
                          LEFT JOIN `chart_type` ctt1 ON pct1.`chart_type_id` = ctt1.`id`
                          LEFT JOIN `chart_status` cs1 ON cs1.`chart_type_id` = ctt1.`id`
                          LEFT JOIN `chart_status` cs_ordinal1 ON cs_ordinal1.`id` = pct1.`chart_status_id`
                          WHERE ctt1.`description` IN(
                            'HSAT',
                            'HSAT Curbside',
                            'HSAT Curbside (Repeat)',
                            'HSAT (Mobile)',
                            'HSAT Mobile (Repeat)'
                          )
                          AND cs1.`description` IN('Booked', 'Completed')
                          AND pct1.`patient_id` = p.`id`
                          AND pct1.`is_deleted` = 0
                          ORDER BY pct1.`chart_date` DESC, cs_ordinal1.`ordinal` DESC
                          LIMIT 1
                        )
                        LEFT JOIN `provider` prov ON p.`provider_id` = prov.`id`
                        LEFT JOIN `provider` pch_prov ON pch.`provider_id` = pch_prov.`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` DESC
                          LIMIT 1
                        )
                        LEFT JOIN `health_facility` hfpb ON pb.`health_facility_id` = hfpb.`id` 
                        LEFT JOIN `chart_type` cth ON pch.`chart_type_id` = cth.`id`
                        LEFT JOIN `chart_status` csh ON pch.`chart_status_id` = csh.`id`                    
                        WHERE p.`is_deleted` = 0
                        $sqlWhere3
                        AND p.`is_duplicate` = 0
                        AND p.`full_name` IS NOT NULL
                        AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                        AND hf.`description` IS NOT NULL
                        AND p.`referral_date` IS NOT NULL
                        AND p.`referral_date` != '0000-00-00 00:00:00'";

        $conn = $this->getEntityManager()->getConnection();

        $query1 = $conn->prepare($sql1);
        $query2 = $conn->prepare($sql2);
        $query3 = $conn->prepare($sql_referralCount);
        $query4 = $conn->prepare($sql_invoices);

        $query1->execute();
        $query2->execute($stmtParams);
        $query3->execute($stmtParams2);
        $query4->execute($stmtParams3);

        $result1 = $query1->fetchAll();
        $result2 = $query2->fetchAll();
        $result3 = $query3->fetch();
        $result4 = $query4->fetch();

        $totalReferral = $result3['totalReferral'];
        $totalInvoice = $result4['invoicesCount'];
        $patientChartCount = array();
        $hsatCnt = 0;
        $hsatMobileCnt = 0;
        $hriCnt = 0;
        $cpapSetupCnt = 0;
        $cpapSetupSoldCnt = 0;
        $cpapEvalCnt = 0;
        $cpapEndEvalCnt = 0;

        foreach ($result1 as $key1 => $chartTypes) {
            $chartTypeCount = array();
            $percentage = 0;
            $invoicePercentage = 0;
            foreach ($result2 as $key2 => $patientCharts) {
                if($chartTypes['chart_type_id'] == $patientCharts['chart_type_id']) {

                    if ($totalReferral > 0 || $cpapSetupCnt > 0) {
                        if ($patientCharts['chart_status_description'] == 'Completed') {
                            if ($chartTypes['chartType'] == 'HSAT') {
                                $hsatCnt = $patientCharts['cnt'];
                                $percentage = $patientCharts['cnt'] / $totalReferral * 100;
                            }

                            if ($chartTypes['chartType'] == 'HSAT (Mobile)') {
                                $hsatMobileCnt = $patientCharts['cnt'];
                                $percentage = $patientCharts['cnt'] / $totalReferral * 100;
                            }

                            if ($chartTypes['chartType'] == 'HSAT Results Interpretation') {
                                $hriCnt = $patientCharts['cnt'];

                                if($hsatCnt != 0) {
                                    $percentage = $patientCharts['cnt'] / $hsatCnt * 100;
                                } else if($hsatMobileCnt != 0) {
                                        $percentage = $patientCharts['cnt'] / $hsatMobileCnt * 100;
                                } else {
                                    $percentage = 0;
                                }

                            }

                            if ($chartTypes['chartType'] == 'CPAP Setup') {
                                $cpapSetupCnt = $patientCharts['cnt'];

                                if($hriCnt != 0) {;
                                    $percentage = $patientCharts['cnt'] / $hriCnt * 100;

                                } else {
                                    $percentage = 0;
                                }

                                $invoicePercentage = $totalInvoice / $patientCharts['cnt'] * 100;
                            }

//                            if ($chartTypes['chartType'] == 'CPAP Eval') {
//                                $cpapEvalCnt = $patientCharts['cnt'];
//                                $percentage = $patientCharts['cnt'] / $cpapSetupSoldCnt * 100;
//                            }
//
//                            if ($chartTypes['chartType'] == 'CPAP End Eval') {
//                                $cpapEndEvalCnt = $patientCharts['cnt'];
//                                $percentage = $patientCharts['cnt'] / $cpapEvalCnt * 100;
//                            }
                        }
                    }



                    $chartTypeCount[] = array(
                        'chart_type_id' => $patientCharts['chart_type_id'],
                        'patient_chart_count' => array(
                            'chart_status_id' => $patientCharts['chart_status_id'],
                            'chart_status' => $patientCharts['chart_status_description'],
                            'chart_status_count' => $patientCharts['cnt'],
                        )
                    );
                }
            }

            if($chartTypes['chartType'] == 'HSAT' || $chartTypes['chartType'] == 'HSAT (Mobile)' || $chartTypes['chartType'] == 'HSAT Results Interpretation' || $chartTypes['chartType'] == 'CPAP Setup') {
                if (count($chartTypeCount)) {
                    $patientChartCount[] = array(
                        'invoices' => $totalInvoice,
                        'invoicePercentage' => $invoicePercentage,
                        'referrals' => $totalReferral,
                        'description' => $chartTypes['chartType'],
                        'chart_types' => $chartTypeCount,
                        'percentage' => $percentage

                    );
                }
            }

        }

        return $patientChartCount;
    }

    public function reportStatusUnderRebookingReport($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.`updated_at` 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['accountUser']) && !empty($params['accountUser']) && 'NULL' !== strtoupper($params['accountUser'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "au.`id` = :accountUser";
            $stmtParams['accountUser'] = $params['accountUser'];
        }

        $sql = "
            SELECT
					 au.`id` accountUserId,
					 au.`full_name` accountUser,
					 cs.`description` chartStatus,
                     COUNT(cs.`description`) AS statusCount
            FROM `patient_chart` pc
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN `patient` p ON pc.`patient_id` = p.`id`
            LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`
            LEFT JOIN `account_user` au ON pc.`updated_by` = au.`username`
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            LEFT JOIN `internal_status` ins ON pc.`internal_status_id` = ins.`id`
            LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`
            LEFT JOIN (
                  SELECT pct.`id`, pct.`patient_id`, pct.`chart_type_id`, MAX(pct.`chart_date`) AS chart_date
                  FROM `patient_chart` pct
                  WHERE pct.`is_deleted` = 0
                  GROUP BY pct.`patient_id`, pct.`chart_type_id`
            ) pct ON pc.`patient_id` = pct.`patient_id` AND pc.`chart_type_id` = pct.`chart_type_id` 
            LEFT JOIN (
                  SELECT pcn.`id`, pcn.`patient_chart_id`, MAX(pcn.`created_at`) AS created_at
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` =  pc.`id`
                  WHERE pcn.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`
             ) pcnt ON pcnt.`patient_chart_id` = pc.`id`
             LEFT JOIN (
                  SELECT pcn.`note`, pcn.`patient_chart_id`, pcn.`created_at`, pcn.`created_by`
                  FROM patient_chart_note pcn
                  LEFT JOIN `patient_chart` pc ON pcn.`patient_chart_id` = pc.`id`
                  WHERE pcn.`is_deleted` = 0
                  GROUP BY pcn.`patient_chart_id`, pcn.`created_at`
             ) pcn ON pcn.`patient_chart_id` = pc.`id` AND pcnt.`created_at` = pcn.`created_at`
             LEFT JOIN(
                  SELECT pn.`note`, pn.`patient_id` patientId, pn.`created_by`, pn.`created_at`
                  FROM `patient_note` pn
                  WHERE pn.`is_deleted` = 0
                  GROUP BY pn.`created_at`
                  ORDER BY pn.`created_at` DESC
             ) pnote ON pnote.`patientId` = p.`id`
             $sqlWhere
            AND pc.`chart_date` != '0000-00-00 00:00:00'   
            AND (pc.`is_deleted` = '0' OR pc.`is_deleted` IS NULL)
            AND cs.`description` IN ('No Show', 'Cancelled')
            AND p.`is_active` = 1
            AND pc.`is_deleted` = 0
            AND IF( pc.`internal_status_id` IS NOT NULL, ins.`remove_from_dashboard` = 0, 0=0)
            AND (pct.`id` IS NULL OR pc.`chart_date` >= pct.`chart_date`)
            GROUP BY cs.`description`, au.id
            ORDER BY au.`full_name` ASC";

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

        $result = $query->fetchAll();

        return $result;
    }

    public function isAssessments() {

        ini_set('memory_limit', '2048M');
        set_time_limit(0);

        $sql = "
            SELECT
              ct.`description` AS chartTypeAssessment
            FROM `chart_type` ct
            WHERE ct.`is_assessment` = 1
           ";

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

        $result = $query->fetchAll();

        return $result;
    }


    public function reportAppointmentHistory($params) {
        $sqlWhere = "WHERE  pat.`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['patient']) && !empty($params['patient']) && 'NULL' !== strtoupper($params['patient'])) {
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "pc.`patient_id` = :patient";
            $stmtParams['patient'] = $params['patient'];
        }

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

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

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

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

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

        $sql = "
            SELECT 
                pat.`id` patientId,
                pat.`full_name` patientName,
                ct.`description` chartType,
                DATE_FORMAT(pc.`chart_date`, '%Y/%m/%d') chartDate,
                cs.`description` chartStatus,
                CONCAT('[ ' , DATE_FORMAT(pcn.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', pcn.`created_by`, ' ] : ', pcn.`note`) AS note
            FROM `patient_chart` pc
            LEFT JOIN `patient` pat ON pat.`id` = pc.`patient_id`
            LEFT JOIN (SELECT
                          phf.`patient_id` AS patientId,
                          phf.`health_facility_id` AS healthFacilityId
                       FROM `patient_health_facility` phf
                       LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
                       WHERE phf.`end_date` IS NULL
                       ORDER BY phf.`created_at` DESC
            ) temp_hf ON temp_hf.`patientId` = pat.`id`
            LEFT JOIN `health_facility` hf ON temp_hf.`healthFacilityId` = hf.`id`
            LEFT JOIN `company` co ON hf.`company_id` = co.`id`
            LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
            LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`
            LEFT JOIN `patient_chart_note` pcn ON pcn.`id` = (
					SELECT 
						pcn2.`id` 
					FROM `patient_chart_note` pcn2
					WHERE pcn2.`patient_chart_id` = pc.`id`
					ORDER BY pcn2.`created_at` DESC
					LIMIT 1
			)
            $sqlWhere
            AND pat.`id` IS NOT NULL
            AND pat.`full_name` IS NOT NULL
            AND pat.`full_name` != ''
            ORDER BY pat.`full_name` ASC, pc.`chart_date` ASC
        ";

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

        $result = $query->fetchAll();

        return $result;
    }

    public function reportPatientDataTransfer($params) {
        $sqlWhere = "WHERE p.`is_deleted` = 0";
        $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(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) ";
                    }
                }
            }
        }

        switch ($params['is_patient_data_transfer']) {
            case 'All':
                $sqlWhere .= " ";
                break;
            case '1':
                $sqlWhere .= " AND p.`id` IN (
                    SELECT pc2.`patient_id`
                    FROM `patient_chart` pc2
                    LEFT JOIN `chart_type` ct1 ON pc2.`chart_type_id` = ct1.`id`
                    WHERE pc2.`is_deleted` = 0
                    AND ct1.`description` = 'Patient Data Transfer - CBT' 
                )";
                break;
            case '0':
                $sqlWhere .= " AND p.`id` NOT IN (
                    SELECT pc2.`patient_id`
                    FROM `patient_chart` pc2
                    LEFT JOIN `chart_type` ct1 ON pc2.`chart_type_id` = ct1.`id`
                    WHERE pc2.`is_deleted` = 0
                    AND ct1.`description` = 'Patient Data Transfer - CBT' 
                )";
                break;
        }

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

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

        $result = $query->fetchAll();

        return $result;
    }

    public function checkExistInitialAssessment($params) {
        $stmtParams = array();
        $stmtParams['patient_id'] = $params['patient_id'];

        $sql = "
             SELECT 
              pc.`id` AS patient_chart_id,
              pctm.`initial_rdi`, 
              pctm.`initial_ahi`, 
              pctm.`initial_berlin_questionnaire_score`, 
              pctm.`initial_epworth_sleepness_scale`,
              pctm.`time_ninety_percent_spotwo`,
              pctm.`initial_mean_spotwo`
            FROM `patient_chart_type_method` pctm
            LEFT JOIN `patient_chart` pc ON pctm.`patient_chart_id` = pc.`id`
            LEFT JOIN `chart_type` c ON pc.`chart_type_id` = c.`id`
            WHERE pctm.`is_deleted` = 0
            AND pc.`patient_id` = :patient_id
            AND (pctm.`initial_rdi` IS NOT NULL 
				  OR pctm.`initial_ahi` IS NOT NULL 
				  OR pctm.`initial_berlin_questionnaire_score` IS NOT NULL 
				  OR pctm.`initial_epworth_sleepness_scale` IS NOT NULL 
				  OR pctm.`time_ninety_percent_spotwo` IS NOT NULL 
				  OR pctm.`initial_mean_spotwo` IS NOT NULL 
				)
            ORDER BY pctm.`created_at` ASC
            LIMIT 1
        ";

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

        $result = $query->fetch();

        return $result;
    }

    public function patientProfileAssessment($params) {
        $stmtParams = array();
        $stmtParams['patient_id'] = $params;

        $sql = "
             SELECT 
              pc.`id` AS patient_chart_id,
              pctm.`initial_rdi`, 
              pctm.`initial_ahi`, 
              pctm.`initial_berlin_questionnaire_score`, 
              pctm.`initial_epworth_sleepness_scale`,
              pctm.`time_ninety_percent_spotwo`,
              pctm.`initial_mean_spotwo`,
              pctm.`mask_leak`,
              DATE_FORMAT(pctm.`last_device_replaced`, '%m/%d/%Y') AS last_device_replaced,
              pctm.`neck_size`,
              IF(pctm3.`id` IS NOT NULL,  
                    (
                      SELECT pctm2.`pressure_range_from`
                      FROM `patient_chart_type_method` pctm2 
                      LEFT JOIN `patient_chart` pc2 ON pctm2.`patient_chart_id` = pc2.`id`
                      WHERE pctm2.`is_deleted` = 0
                      AND pc2.`patient_id` = pc.`patient_id`
                      ORDER BY pctm2.`created_at` DESC
                      LIMIT 1
                    ),
			  	    pctm.`pressure_range_from`
			  )	AS pressureRangeFrom,
			  IF(pctm3.`id` IS NOT NULL,
                    (
                      SELECT pctm2.`pressure_range_to`
                      FROM `patient_chart_type_method` pctm2 
                      LEFT JOIN `patient_chart` pc2 ON pctm2.`patient_chart_id` = pc2.`id`
                      WHERE pctm2.`is_deleted` = 0
                      AND pc2.`patient_id` = pc.`patient_id`
                      ORDER BY pctm2.`created_at` DESC
                      LIMIT 1
                    ),
                    pctm.`pressure_range_to`
			  ) AS pressureRangeTo,
			  IF(pctm3.`id` IS NOT NULL,
			        (
			          SELECT pctm2.`average_cpap_usage`
              		  FROM `patient_chart_type_method` pctm2 
              		  LEFT JOIN `patient_chart` pc2 ON pctm2.`patient_chart_id` = pc2.`id`
              		  WHERE pctm2.`is_deleted` = 0
              		  AND pc2.`patient_id` = pc.`patient_id`
              		  ORDER BY pctm2.`created_at` DESC
              		  LIMIT 1
			  		),
			  		pctm.`average_cpap_usage`
			  ) AS avgCpapUsage,
			  IF(pctm3.`id` IS NOT NULL,
					(
			          SELECT pctm2.`current_rdi`
                      FROM `patient_chart_type_method` pctm2 
                      LEFT JOIN `patient_chart` pc2 ON pctm2.`patient_chart_id` = pc2.`id`
                      WHERE pctm2.`is_deleted` = 0
                      AND pc2.`patient_id` = pc.`patient_id`
                      ORDER BY pctm2.`created_at` DESC
                      LIMIT 1
			        ),
			  		pctm.`current_rdi`
			  ) AS currentRdi,
			  IF(pctm3.`id` IS NOT NULL,
			        (
			          SELECT pctm2.`current_ahi`
                      FROM `patient_chart_type_method` pctm2 
                      LEFT JOIN `patient_chart` pc2 ON pctm2.`patient_chart_id` = pc2.`id`
                      WHERE pctm2.`is_deleted` = 0
                      AND pc2.`patient_id` = pc.`patient_id`
                      ORDER BY pctm2.`created_at` DESC
                      LIMIT 1
			        ),
			        pctm.`current_ahi`
			  ) AS currentAhi,
			  IF(pctm3.`id` IS NOT NULL,
			        (
			          SELECT pctm2.`ess`
                      FROM `patient_chart_type_method` pctm2 
                      LEFT JOIN `patient_chart` pc2 ON pctm2.`patient_chart_id` = pc2.`id`
                      WHERE pctm2.`is_deleted` = 0
                      AND pc2.`patient_id` = pc.`patient_id`
                      ORDER BY pctm2.`created_at` DESC
                      LIMIT 1
			        ),
			        pctm.`ess`
			  ) AS ess
            FROM `patient_chart_type_method` pctm
            LEFT JOIN `patient_chart` pc ON pctm.`patient_chart_id` = pc.`id`
            LEFT JOIN `chart_type` c ON pc.`chart_type_id` = c.`id`
            LEFT JOIN `patient_chart_type_method` pctm3 ON pctm3.`id` = (
					SELECT pctm4.`id`
              	FROM `patient_chart_type_method` pctm4
              	LEFT JOIN `patient_chart` pc3 ON pctm4.`patient_chart_id` = pc3.`id`
              	WHERE pctm3.`is_deleted` = 0
              	AND pc3.`patient_id` = pc.`patient_id`
              	ORDER BY pctm4.`created_at` DESC
              	LIMIT 1
				)
            WHERE pctm.`is_deleted` = 0
            AND pc.`patient_id` = :patient_id
            AND (pctm.`initial_rdi` IS NOT NULL 
				  OR pctm.`initial_ahi` IS NOT NULL 
				  OR pctm.`initial_berlin_questionnaire_score` IS NOT NULL 
				  OR pctm.`initial_epworth_sleepness_scale` IS NOT NULL 
				  OR pctm.`time_ninety_percent_spotwo` IS NOT NULL 
				  OR pctm.`initial_mean_spotwo` IS NOT NULL 
				)
            ORDER BY pc.`chart_date` DESC
            LIMIT 1
        ";

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

        $result = $query->fetch();

        return $result;
    }


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

    public function rx_patient_encounter($userId, $patientId){

        $columns = array(
            array('hf.`description`', 'hf.`description`', 'health_facility_desc'),
            array('hf.`description`', 'hf.`description`', 'branchDesc'),
            array('pc.`id`', "pc.`id`"),
            array('ct.`description`', 'ct.`description`', 'chart_type_desc'),
            array('pc.`chart_date`', "DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y')", 'chart_date'),
            array('ct.`description`', 'ct.`description`', 'typeDesc'),
            array('ct.`id`', 'ct.`id`', 'typeId'),
            array('pc.`chart_date`', "DATE_FORMAT(pc.`chart_date`, '%m/%d/%Y')", 'date'),
            array('pr.`full_name`', 'pr.`full_name`', 'provider_fullName'),
            array('cs.`description`', 'cs.`description`', 'chart_status_desc'),
            array('cs.`description`', 'cs.`description`', 'statusDesc'),
            array('cs.`id`', 'cs.`id`', 'statusId'),
            array('pc.`patient_id`', 'pc.`patient_id`'),
        );

        $select = "SELECT";
        $from = "FROM `patient_chart` pc";
        $joins = "LEFT JOIN `health_facility` hf ON pc.`health_facility_id` = hf.`id`";
        $joins .= " LEFT JOIN `provider` pr ON pc.`provider_id` = pr.`id`";
        $joins .= " LEFT JOIN `chart_status` cs ON pc.`chart_status_id` = cs.`id`";
        $joins .= " LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`";
        $sqlWhere = "WHERE pc.`patient_id` = :patient_id";
        $sqlWhere .= " AND (pc.`is_deleted` = 0 OR pc.`is_deleted` IS NULL)";
        $groupBy = "";
        $orderBy = " ORDER BY pc.`chart_date` 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();
    }


    public function check_patientChart_healthFacility($params) {
        $sql = "SELECT pc.`health_facility_id`
                FROM `patient_chart` pc
                WHERE pc.`patient_id` = :patient
                AND pc.`is_deleted` = 0
                ORDER BY pc.`chart_date` DESC
                LIMIT 1";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute(array(
            'patient' => $params
        ));


        return $query->fetch();
    }

}
