<?php

namespace RxConnexionBundle\Repository;

use RxConnexionBundle\Entity\FaxTranEntity;

/**
 * FaxTranRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class FaxTranRepository extends \Doctrine\ORM\EntityRepository
{

    public function validate($faxTran_form) {

        $errors = array();

        $action = $faxTran_form['action'];

        // d = delete
        if($action !== 'd') {
            /*if(empty($faxTran_form['health_facility'])){
                $errors[] = 'Health facility should not be empty';
            }*/
//            if(empty($faxTran_form['fax_category'])){
//                $errors[] = 'Category should not be empty';
//            }
//            if(empty($faxTran_form['fax_status'])){
//                $errors[] = 'Status should not be empty';
//            }
        }

        return $errors;
    }


    public function ajax_list(array $get){

        $columns = array(
            array('f.`fax_date`', "DATE_FORMAT(f.`fax_date`, '%m/%d/%Y %h:%i %p')", 'fax_date'),
            array('f.`remote_id`', 'f.`remote_id`'),
            array('f.`fax_category`', "f.`fax_category`"),
            array('temp_pat.`patient`', "temp_pat.`patient`"),
            array('hf.`description`', "hf.`description`", 'health_facility'),
            array('f.`pages`', "f.`pages`"),
            array('au.`full_name`', 'au.`full_name`', 'assignedTo'),
            array('f.`fax_status`', 'f.`fax_status`'),
            array('f.`id`', 'f.`id`'),
            array('f.`fax_id`', 'f.`fax_id`'),
            array('f.`account_user_id`', 'f.`account_user_id`'),
        );
        $asColumns = array(
            'DATE_FORMAT(f.`fax_date`, \'%m/%d/%Y %h:%i %p\')',
            'CONCAT(ROUND(CAST(f.`size` AS UNSIGNED)/1000,0),"KB")',
        );

        $select = "SELECT";
        $from = "FROM `fax_tran` f";
        $joins = "LEFT JOIN `health_facility` hf ON f.`health_facility_id` = hf.`id`";
        $joins .= " LEFT JOIN `account_user` au ON f.`account_user_id` = au.`id`";
        $joins .= " LEFT JOIN (SELECT
                                    ref.`fax_tran_id` faxTranId,
                                    pat.`full_name` patient
                               FROM `referral` ref
                               LEFT JOIN `fax_tran` ft ON ft.`id` = ref.`fax_tran_id`
                               LEFT JOIN `patient` pat ON pat.`referral_id` = ref.`id`
                    ) temp_pat ON temp_pat.`faxTranId` = f.`id`";
        $sqlWhere = "WHERE (f.`user_fax_number` IS NULL OR f.`user_fax_number` = :default_fax_number)";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array();
        $stmtParams['default_fax_number'] = FaxTranEntity::DEFAULT_FAX_NUMBER;

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

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

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

        /* 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),
            '$sql' => $sql
        );
    }

    public function unite_ajax_list(array $get){

        $columns = array(
            array('f.`fax_date`', "DATE_FORMAT(f.`fax_date`, '%m/%d/%Y %h:%i %p')", 'fax_date'),
            array('f.`remote_id`', 'f.`remote_id`'),
            array('f.`fax_category`', "f.`fax_category`"),
            array('temp_pat.`patient`', "temp_pat.`patient`"),
            array('hf.`description`', "hf.`description`", 'health_facility'),
            array('f.`pages`', "f.`pages`"),
            array('au.`full_name`', 'au.`full_name`', 'assignedTo'),
            array('f.`fax_status`', 'f.`fax_status`'),
            array('f.`id`', 'f.`id`'),
            array('f.`fax_id`', 'f.`fax_id`'),
            array('f.`account_user_id`', 'f.`account_user_id`'),
        );
        $asColumns = array(
            'DATE_FORMAT(f.`fax_date`, \'%m/%d/%Y %h:%i %p\')',
            'CONCAT(ROUND(CAST(f.`size` AS UNSIGNED)/1000,0),"KB")',
        );

        $select = "SELECT";
        $from = "FROM `fax_tran` f";
        $joins = "LEFT JOIN `health_facility` hf ON f.`health_facility_id` = hf.`id`";
        $joins .= " LEFT JOIN `account_user` au ON f.`account_user_id` = au.`id`";
        $joins .= " LEFT JOIN (SELECT
                                    ref.`fax_tran_id` faxTranId,
                                    pat.`full_name` patient
                               FROM `referral` ref
                               LEFT JOIN `fax_tran` ft ON ft.`id` = ref.`fax_tran_id`
                               LEFT JOIN `patient` pat ON pat.`referral_id` = ref.`id`
                    ) temp_pat ON temp_pat.`faxTranId` = f.`id`";
        $sqlWhere = "WHERE f.`user_fax_number` = :unite_fax_number";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array();
        $stmtParams['unite_fax_number'] = FaxTranEntity::UNITE_FAX_NUMBER;

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

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

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

        /* 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),
            '$sql' => $sql
        );
    }

    public function fax_outbound_ajax_list(array $get){

        $columns = array(
            array('fto.`created_at`', "DATE_FORMAT(fto.`created_at`, '%m/%d/%Y %h:%i %p')", 'createdAt'),
            array('IF(fto.`patient_letter_id` IS NOT NULL, "Patient Letter", "Appointment Notice")', "IF(fto.`patient_letter_id` IS NOT NULL, 'Patient Letter', 'Appointment Notice')", 'faxOutboundType'),
            array('fto.`fax_no`', "fto.`fax_no`", 'faxNo'),
            array('pl.`letter_type`', "pl.`letter_type`", 'letter_type'),
            array('fto.`fax_type`', "fto.`fax_type`", 'faxType'),
            //array('fto.`status`', "fto.`status`", 'status'),
            array('fto.`file_name`', "fto.`file_name`", 'file_name'),
            array('fto.`patient_letter_id`', "fto.`patient_letter_id`", 'patient_letter_id'),
            array('fto.`appointment_notice_fax_id`', "fto.`appointment_notice_fax_id`", 'appointment_notice_fax_id'),
            array('fto.`id`', "fto.`id`"),
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `fax_tran_outbound` fto";
        $joins = "LEFT JOIN `patient_letter` pl ON pl.`id` = fto.`patient_letter_id`";
        $joins .= " LEFT JOIN `appointment_notice_fax` anf ON fto.`appointment_notice_fax_id` = anf.`id`";
        $sqlWhere = "WHERE fto.`is_deleted` = 0";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array();

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

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

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

        /* 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),
            '$sql' => $sql
        );
    }
    
    //DASHBOARD
    public function dashboardNewFaxes() {

        $conn = $this->getEntityManager()->getConnection();
        $sql = $conn->prepare(
            "
            SELECT
                ft.`id` AS faxTranId
            FROM `fax_tran` ft
            WHERE ft.`fax_status` = 0
        "
        );
        $sql->execute();
        return $sql->fetchAll();
    }

    public function dashboardNewFaxesCount() {

        $conn = $this->getEntityManager()->getConnection();
        $sql = $conn->prepare(
            "
            SELECT
                COUNT(DISTINCT ft.`id`) AS data
            FROM `fax_tran` ft
            WHERE ft.`fax_status` = 0
        "
        );
        $sql->execute();
        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }

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

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

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

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

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

                $stmtParams['userId'] = $userData['id'];
                $stmtParams['now'] = date('Y-m-d');
            }
        }

        $sql = $conn->prepare(
            "
            SELECT
                p.`full_name` AS fullName,
                p.`id` AS patientId,
                hf.`description` as healthFacility,
                DATE_FORMAT(r.`referral_date`, '%m/%d/%Y') AS referralDate,
                CONCAT('[ ' , DATE_FORMAT(pn.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', pn.`created_by`, ' ] : ', pn.`note`) AS latestNotes,
                IF(DATE_FORMAT(NOW(), '%m/%d/%Y') > DATE_FORMAT(DATE_ADD(pn.`created_at`, INTERVAL 1 DAY), '%m/%d/%Y')   , 'red', NULL) noteInterval
            FROM `patient` p
            LEFT JOIN `referral` r ON p.`referral_id` = r.`id`
            LEFT JOIN `fax_tran` f ON r.`fax_tran_id` = f.`id`
            LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
              SELECT phf2.`id`
              FROM `patient_health_facility` phf2
              WHERE phf2.`patient_id` = p.`id`
              AND phf2.`end_date` IS NULL
              AND phf2.`is_deleted` = 0
              ORDER BY phf2.`start_date` DESC
              LIMIT 1
            )
            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
            LEFT JOIN (
                  SELECT pn.`id`, pn.`patient_id`, MAX(pn.`created_at`) AS created_at
                  FROM patient_note pn
                  GROUP BY pn.`patient_id`
             ) pnt ON pnt.`patient_id` = p.`id`
             LEFT JOIN (
                  SELECT pn.`note`, pn.`patient_id`, pn.`created_at`, pn.`created_by`
                  FROM patient_note pn
                  GROUP BY pn.`patient_id`, pn.`created_at`
             ) pn ON pn.`patient_id` = p.`id` AND pnt.`created_at` = pn.`created_at`
             LEFT JOIN (
	              SELECT
		          COUNT(DISTINCT pc.`id`) AS chartCtr,
                  ct.`description` AS chartType_desc,
                  pc.`patient_id`
                FROM `patient_chart` pc
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                GROUP BY pc.`patient_id`
            ) pc ON p.`id` = pc.`patient_id`
            WHERE (p.`id` NOT IN (
              SELECT pc.`patient_id`
              FROM `patient_chart` pc
              LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
              WHERE pc.`patient_id` = p.`id`
              AND pc.`is_deleted` = 0
              GROUP BY pc.`patient_id`
            ) OR pc.`chartCtr` = 1 AND pc.`chartType_desc` = 'Intro Phone Call')
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
            $andWhere
            AND r.`fax_tran_id` IS NOT NULL
            AND p.`id` NOT IN (
               SELECT pt.`patient_id`
               FROM `patient_ticket` pt
               LEFT JOIN `patient` pat2 ON pt.`patient_id` = pat2.`id`
               WHERE pat2.`is_active` = 1
               AND pat2.`is_deleted` = 0
             )
            ORDER BY pn.`created_at`, pn.`note`
        "
        );
        $sql->execute($stmtParams);
        return $sql->fetchAll();
    }

    public function dashboardWithFaxNoEncountersSummary($params, $userData) {
        $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 ').'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(r.`referral_date`) = :month';
            $stmtParams['month'] = $month;
        }

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

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

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

                $stmtParams['userId'] = $userData['id'];
                $stmtParams['now'] = date('Y-m-d');
            }
        }

        $sql = $conn->prepare(
            "
            SELECT
				CONCAT_WS(' : ', EXTRACT(YEAR FROM r.`referral_date`), CONVERT(COUNT(EXTRACT(YEAR FROM r.`referral_date`)), INT)) summaryCount
            FROM `patient` p
            LEFT JOIN `referral` r ON p.`referral_id` = r.`id`
            LEFT JOIN `fax_tran` f ON r.`fax_tran_id` = f.`id`
            LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
              SELECT phf2.`id`
              FROM `patient_health_facility` phf2
              WHERE phf2.`patient_id` = p.`id`
              AND phf2.`end_date` IS NULL
              AND phf2.`is_deleted` = 0
              ORDER BY phf2.`start_date` DESC
              LIMIT 1
            )
            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id` 
            LEFT JOIN (
                  SELECT pn.`id`, pn.`patient_id`, MAX(pn.`created_at`) AS created_at
                  FROM patient_note pn
                  GROUP BY pn.`patient_id`
             ) pnt ON pnt.`patient_id` = p.`id`
             LEFT JOIN (
                  SELECT pn.`note`, pn.`patient_id`, pn.`created_at`, pn.`created_by`
                  FROM patient_note pn
                  GROUP BY pn.`patient_id`, pn.`created_at`
             ) pn ON pn.`patient_id` = p.`id` AND pnt.`created_at` = pn.`created_at`
             LEFT JOIN (
	              SELECT
		          COUNT(DISTINCT pc.`id`) AS chartCtr,
                  ct.`description` AS chartType_desc,
                  pc.`patient_id`
                FROM `patient_chart` pc
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                GROUP BY pc.`patient_id`
            ) pc ON p.`id` = pc.`patient_id`
            WHERE (p.`id` NOT IN (
              SELECT pc.`patient_id`
              FROM `patient_chart` pc
              LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
              WHERE pc.`patient_id` = p.`id`
              AND pc.`is_deleted` = 0
              GROUP BY pc.`patient_id`
            ) OR pc.`chartCtr` = 1 AND pc.`chartType_desc` = 'Intro Phone Call')
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
            $andWhere
            AND r.referral_date IS NOT NULL
            AND r.`fax_tran_id` IS NOT NULL
            AND EXTRACT(YEAR FROM r.`referral_date`) >= '2014'
            AND p.`id` NOT IN (
               SELECT pt.`patient_id`
               FROM `patient_ticket` pt
               LEFT JOIN `patient` pat2 ON pt.`patient_id` = pat2.`id`
               WHERE pat2.`is_active` = 1
               AND pat2.`is_deleted` = 0
             )
            GROUP BY EXTRACT(YEAR FROM r.`referral_date`)
        "
        );
        $sql->execute($stmtParams);
        return $sql->fetchAll();
    }

    public function dashboardWithFaxNoEncountersCount() {
        $conn = $this->getEntityManager()->getConnection();
        $sql = $conn->prepare(
            "
            SELECT
                COUNT(DISTINCT p.`id`) AS data
            FROM `patient` p
            LEFT JOIN `referral` r ON p.`referral_id` = r.`id`
              LEFT JOIN `fax_tran` f ON r.`fax_tran_id` = f.`id`
            WHERE p.`id` NOT IN (
              SELECT pc.`patient_id`
              FROM `patient_chart` pc
              WHERE pc.`patient_id` = p.`id`
              AND pc.`is_deleted` = 0
              GROUP BY pc.`patient_id`
            )
            AND r.`fax_tran_id` IS NOT NULL
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
        "
        );
        $sql->execute();
        return $sql->rowCount() ? $sql->fetch(0)['data'] : 0;
    }


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

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

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

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

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

                $stmtParams['userId'] = $userData['id'];
                $stmtParams['now'] = date('Y-m-d');
            }
        }

        $sql = $conn->prepare(
            "
            SELECT
                p.`full_name` AS fullName,
                p.`id` AS patientId,
                hf.`description` as healthFacility,
                DATE_FORMAT(r.`referral_date`, '%m/%d/%Y') AS referralDate,
                CONCAT('[ ' , DATE_FORMAT(pn.`created_at`, '%m/%d/%Y %h:%i %p'), ' - ', pn.`created_by`, ' ] : ', pn.`note`) AS latestNotes,
                IF(DATE_FORMAT(NOW(), '%m/%d/%Y') > DATE_FORMAT(DATE_ADD(pn.`created_at`, INTERVAL 1 DAY), '%m/%d/%Y')   , 'red', NULL) noteInterval
            FROM `patient` p
            LEFT JOIN `referral` r ON p.`referral_id` = r.`id`
            LEFT JOIN `fax_tran` f ON r.`fax_tran_id` = f.`id`
            LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
              SELECT phf2.`id`
              FROM `patient_health_facility` phf2
              WHERE phf2.`patient_id` = p.`id`
              AND phf2.`end_date` IS NULL
              AND phf2.`is_deleted` = 0
              ORDER BY phf2.`start_date` DESC
              LIMIT 1
            )
            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
            LEFT JOIN `patient_note` pn ON pn.`id` = (
              SELECT pn1.`id`
              FROM `patient_note` pn1
              WHERE pn1.`is_deleted` = 0
              AND pn1.`patient_id` = p.`id`
              ORDER BY pn1.`created_at` DESC
              LIMIT 1
            )
             LEFT JOIN (
	              SELECT
		          COUNT(DISTINCT pc.`id`) AS chartCtr,
                  ct.`description` AS chartType_desc,
                  pc.`patient_id`
                FROM `patient_chart` pc
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                GROUP BY pc.`patient_id`
            ) pc ON p.`id` = pc.`patient_id`
            WHERE (p.`id` NOT IN (
              SELECT pc.`patient_id`
              FROM `patient_chart` pc
              LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
              WHERE pc.`patient_id` = p.`id`
              AND pc.`is_deleted` = 0
              GROUP BY pc.`patient_id`
            ) OR pc.`chartCtr` = 1 AND pc.`chartType_desc` = 'Intro Phone Call')
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
            $andWhere
            AND r.`id` IS NOT NULL
            AND p.`id` NOT IN (
               SELECT pt.`patient_id`
               FROM `patient_ticket` pt
               LEFT JOIN `patient` pat2 ON pt.`patient_id` = pat2.`id`
               WHERE pat2.`is_active` = 1
               AND pat2.`is_deleted` = 0
            )
            AND p.`id` NOT IN(
			  SELECT ps.`patient_id`
              FROM `patient_service` ps
              LEFT JOIN `service` s ON s.`id` = ps.`service_id`
              WHERE ps.`is_deleted` = 0
			)
            ORDER BY pn.`created_at`, pn.`note`
        "
        );
        $sql->execute($stmtParams);
        return $sql->fetchAll();
    }

    public function dashboardWithRefferalNoEncountersSummary($params, $userData) {
        $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 ').'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(r.`referral_date`) = :month';
            $stmtParams['month'] = $month;
        }

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

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

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

                $stmtParams['userId'] = $userData['id'];
                $stmtParams['now'] = date('Y-m-d');
            }
        }

        $sql = $conn->prepare(
            "
            SELECT
				CONCAT_WS(' : ', EXTRACT(YEAR FROM r.`referral_date`), CONVERT(COUNT(EXTRACT(YEAR FROM r.`referral_date`)), INT)) summaryCount
            FROM `patient` p
            LEFT JOIN `referral` r ON p.`referral_id` = r.`id`
            LEFT JOIN `fax_tran` f ON r.`fax_tran_id` = f.`id`
            LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
              SELECT phf2.`id`
              FROM `patient_health_facility` phf2
              WHERE phf2.`patient_id` = p.`id`
              AND phf2.`end_date` IS NULL
              AND phf2.`is_deleted` = 0
              ORDER BY phf2.`start_date` DESC
              LIMIT 1
            )
            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
            LEFT JOIN `patient_note` pn ON pn.`id` = (
              SELECT pn1.`id`
              FROM `patient_note` pn1
              WHERE pn1.`is_deleted` = 0
              AND pn1.`patient_id` = p.`id`
              ORDER BY pn1.`created_at` DESC
              LIMIT 1
            )
             LEFT JOIN (
	              SELECT
		          COUNT(DISTINCT pc.`id`) AS chartCtr,
                  ct.`description` AS chartType_desc,
                  pc.`patient_id`
                FROM `patient_chart` pc
                LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
                GROUP BY pc.`patient_id`
            ) pc ON p.`id` = pc.`patient_id`
            WHERE (p.`id` NOT IN (
              SELECT pc.`patient_id`
              FROM `patient_chart` pc
              LEFT JOIN `chart_type` ct ON pc.`chart_type_id` = ct.`id`
              WHERE pc.`patient_id` = p.`id`
              AND pc.`is_deleted` = 0
              GROUP BY pc.`patient_id`
            ) OR pc.`chartCtr` = 1 AND pc.`chartType_desc` = 'Intro Phone Call')
            AND p.`is_active` = 1
            AND p.`is_deleted` = 0
            $andWhere
            AND r.referral_date IS NOT NULL
            AND r.`id` IS NOT NULL
            AND EXTRACT(YEAR FROM r.`referral_date`) >= '2014'
            AND p.`id` NOT IN (
               SELECT pt.`patient_id`
               FROM `patient_ticket` pt
               LEFT JOIN `patient` pat2 ON pt.`patient_id` = pat2.`id`
               WHERE pat2.`is_active` = 1
               AND pat2.`is_deleted` = 0
            )
            AND p.`id` NOT IN(
			  SELECT ps.`patient_id`
              FROM `patient_service` ps
              LEFT JOIN `service` s ON s.`id` = ps.`service_id`
              WHERE ps.`is_deleted` = 0
			)
            GROUP BY EXTRACT(YEAR FROM r.`referral_date`)
        "
        );
        $sql->execute($stmtParams);
        return $sql->fetchAll();
    }

    public function dashboardWithRefferalNoEncountersCount() {
        $conn = $this->getEntityManager()->getConnection();
        $sql = $conn->prepare(
            "
            SELECT
                COUNT(DISTINCT p.`id`) AS data
            FROM `patient` p
            LEFT JOIN `referral` r ON p.`referral_id` = r.`id`
              LEFT JOIN `fax_tran` f ON r.`fax_tran_id` = f.`id`
            WHERE p.`id` NOT IN (
              SELECT pc.`patient_id`
              FROM `patient_chart` pc
              WHERE pc.`patient_id` = p.`id`
              AND pc.`is_deleted` = 0
              GROUP BY pc.`patient_id`
            )
            AND r.`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 reportFax($params) {
        $sqlWhere = "WHERE ft.`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 ") . "ft.`fax_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(isset($params['status']) && 'NULL' !== strtoupper($params['status'])){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "ft.`fax_status` = :status";
            $stmtParams['status'] = $params['status'];
        }

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

        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 
                    ft.`id`,
                    TO_BASE64(p.`id`) AS patientIdEncoded,
                    DATE_FORMAT(ft.`fax_date`, '%m/%d/%Y %h:%i %p') AS faxDate,
                    ft.`fax_category`,
                    IF(r.`full_name` IS NOT NULL, r.`full_name`, p.`full_name`) AS patientName,
                    ft.`remote_id` AS faxNo,
                    co.`description` AS companyDesc,
                    hf.`description` AS branchDesc,
                    au.`full_name` AS assignTo,
                    ft.`file_name`,
                    ft.`pages`,
                    IF(ft.`is_duplicate` IS TRUE, 'Yes', 'No') AS isDuplicate,
                    IF(ft.`is_existing` IS TRUE, 'Yes', 'No') AS isExisting,
                    IF(ft.`is_unable_to_process` IS TRUE, 'Yes', 'No') AS isUnableToProcess
                FROM `fax_tran` ft
                LEFT JOIN `referral` r ON r.`fax_tran_id` = ft.`id`
                LEFT JOIN `patient` p ON p.`id` = ft.`patient_id` OR r.`id` = p.`referral_id`
                LEFT JOIN `health_facility` hf ON hf.`id` = ft.`health_facility_id`
                LEFT JOIN `company` co ON hf.`company_id` = co.`id`
                LEFT JOIN `account_user` au ON au.`id` = ft.`account_user_id` 
                $sqlWhere
                AND hf.`description` IS NOT NULL
                AND hf.`description` NOT IN('DemoLocation', 'test', 'Demo_Test')
                ORDER BY ft.`fax_date` DESC
                ";

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

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }
}
