<?php

namespace RxConnexionBundle\Repository;

use RxConnexionBundle\Entity\ProviderWorkScheduleEntity;

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

        $errors = array();

        $action = $schedule_form['action'];
        // d = delete
        if($action !== 'd') {

            $startTime = strtotime($schedule_form['schedule_date'] . ' ' . sprintf('%002d', $schedule_form['start_time_hour']) . ':' . sprintf('%002d', $schedule_form['start_time_minute']) . ':00 ' . $schedule_form['start_time_period']);
            $endTime = strtotime($schedule_form['schedule_date'] . ' ' . sprintf('%002d', $schedule_form['end_time_hour']) . ':' . sprintf('%002d', $schedule_form['end_time_minute']) . ':00 ' . $schedule_form['end_time_period']);

            $schedules = $this->getEntityManager()->getRepository('RxConnexionBundle:ScheduleEntity')
                ->createQueryBuilder('s')
                ->leftJoin('s.scheduleStatus', 'ss')
                ->where('s.id != :id')
                ->andwhere('s.healthFacility = :healthFacility_id')
                ->andWhere('s.provider = :provider_id')
                ->andWhere('s.scheduleDate = :scheduleDate')
                ->andWhere('ss.description != :scheduleStatus')
                ->andWhere('ss.description != :scheduleStatus2')
                ->andWhere('ss.description != :scheduleStatus3')
                ->andWhere('(s.isDeleted IS NULL OR s.isDeleted != 1)')
                ->setParameter('id', $schedule_form['id'])
                ->setParameter('healthFacility_id', $schedule_form['health_facility'])
                ->setParameter('provider_id', $schedule_form['provider'])
                ->setParameter('scheduleDate', date('Y-m-d', strtotime($schedule_form['schedule_date'])) . ' 00:00:00')
                ->setParameter('scheduleStatus', 'Cancelled')
                ->setParameter('scheduleStatus2', 'No Show')
                ->setParameter('scheduleStatus3', 'Rescheduled')
                ->getQuery()->getResult();

            foreach($schedules as $schedule) {
                $refStartTime = strtotime($schedule->getScheduleDateAsString() . ' ' . sprintf('%002d', $schedule->getStartTimeHour()) . ':' . sprintf('%002d', $schedule->getStartTimeMinute()) . ':00 ' . $schedule->getStartTimePeriod());
                $refEndTime = strtotime($schedule->getScheduleDateAsString() . ' ' . sprintf('%002d', $schedule->getEndTimeHour()) . ':' . sprintf('%002d', $schedule->getEndTimeMinute()) . ':00 ' . $schedule->getEndTimePeriod());
                if(($startTime > $refStartTime && $startTime < $refEndTime) || ($endTime > $refStartTime && $endTime < $refEndTime) || ($startTime <= $refStartTime && $endTime >= $refEndTime)) {

                    $errors[] = 'The selected health facility, provider, schedule date, start time and end time has conflict with an existing schedule from '. $schedule->getStartTime() . ' to ' . $schedule->getEndTime() .'.';
                    break;
                }
            }

            $startTimeConcat = strtotime($schedule_form['start_time_hour'] . ':' . sprintf('%002d',$schedule_form['start_time_minute']) . ' ' . $schedule_form['start_time_period']);
            $endTimeConcat = strtotime($schedule_form['end_time_hour'] . ':' . sprintf('%002d',$schedule_form['end_time_minute']) . ' ' . $schedule_form['end_time_period']);
            if($startTimeConcat >= $endTimeConcat){
                $errors[] = 'Start time cannot be greater than end time';
            }
            if(empty($schedule_form['scheduleStatus'])){
                $errors[] = 'Please select status.';
            }
            if(empty($schedule_form['chartType'])){
                $errors[] = 'Please select chart type.';
            }
            if(empty($schedule_form['patient'])){
                $errors[] = 'Please select patient.';
            }
            if(empty($schedule_form['note'])){
                $errors[] = 'Notes should not be blank.';
            }
            //OUT TYPE

            $providerOut = $this->getEntityManager()->getConnection()->prepare("
                SELECT `id`
                FROM `provider_work_schedule` pws 
                WHERE pws.`provider_id` = :provider_id
                AND pws.`type` = 'Out'
                AND pws.`is_deleted` = 0
                AND STR_TO_DATE(:scheduleDate, '%Y-%m-%d %H:%i:%s') BETWEEN pws.`from_date` AND pws.`to_date` 
                AND (
                        STR_TO_DATE(:startTime, '%l:%i %p') BETWEEN STR_TO_DATE(CONCAT(pws.`from_hr`, ':', LPAD(pws.`from_min`, 2,0), ' ', pws.`from_am`), '%l:%i %p') AND STR_TO_DATE(CONCAT(pws.`to_hr`, ':', LPAD(pws.`to_min`, 2,0), ' ', pws.`to_am`), '%l:%i %p')
                        AND STR_TO_DATE(:endTime, '%l:%i %p') BETWEEN STR_TO_DATE(CONCAT(pws.`from_hr`, ':', LPAD(pws.`from_min`, 2,0), ' ', pws.`from_am`), '%l:%i %p') AND STR_TO_DATE(CONCAT(pws.`to_hr`, ':', LPAD(pws.`to_min`, 2,0), ' ', pws.`to_am`), '%l:%i %p')
                        
                   )
                LIMIT 1
            ");
            $providerOut->execute(array(
                'provider_id' => $schedule_form['provider'],
                'scheduleDate' => date('Y-m-d 00:00:00', strtotime($schedule_form['schedule_date'])),
                'startTime' => $schedule_form['start_time_hour'] . ':' . sprintf('%002d',$schedule_form['start_time_minute']) . ' ' . $schedule_form['start_time_period'],
                'endTime' => $schedule_form['end_time_hour'] . ':' . sprintf('%002d',$schedule_form['end_time_minute']) . ' ' . $schedule_form['end_time_period']
            ));
            $providerOut = $providerOut->fetchAll();
            if(isset($providerOut[0])) {
                $errors[] = 'Physician is out.';
            }

            //IRREGULAR TYPE

            $providerIrreg = $this->getEntityManager()->getConnection()->prepare("
                SELECT 
                    pws.`id`,
                    hf.`description` AS healthFacilityDesc,
                    hf.`id` AS healthFacilityId
                FROM `provider_work_schedule` pws
                LEFT JOIN `health_facility` hf ON pws.`health_facility_id` = hf.`id`
                WHERE pws.`provider_id` = :provider_id
                AND pws.`health_facility_id` != :healthFacility_id
                AND pws.`type` = 'Irregular'
                AND pws.`is_deleted` = 0
                AND STR_TO_DATE(:scheduleDate, '%Y-%m-%d %H:%i:%s') BETWEEN pws.`from_date` AND pws.`to_date`
                AND (
                        STR_TO_DATE(:startTime, '%l:%i %p') BETWEEN STR_TO_DATE(CONCAT(pws.`from_hr`, ':', LPAD(pws.`from_min`, 2,0), ' ', pws.`from_am`), '%l:%i %p') AND STR_TO_DATE(CONCAT(pws.`to_hr`, ':', LPAD(pws.`to_min`, 2,0), ' ', pws.`to_am`), '%l:%i %p')
                        AND STR_TO_DATE(:endTime, '%l:%i %p') BETWEEN STR_TO_DATE(CONCAT(pws.`from_hr`, ':', LPAD(pws.`from_min`, 2,0), ' ', pws.`from_am`), '%l:%i %p') AND STR_TO_DATE(CONCAT(pws.`to_hr`, ':', LPAD(pws.`to_min`, 2,0), ' ', pws.`to_am`), '%l:%i %p') 
                    )
                LIMIT 1
            ");
                $providerIrreg->execute(array(
                    'provider_id' => $schedule_form['provider'],
                    'healthFacility_id' => $schedule_form['health_facility'],
                    'scheduleDate' => date('Y-m-d 00:00:00', strtotime($schedule_form['schedule_date'])),
                    'startTime' => $schedule_form['start_time_hour'] . ':' . sprintf('%002d',$schedule_form['start_time_minute']) . ' ' . $schedule_form['start_time_period'],
                    'endTime' => $schedule_form['end_time_hour'] . ':' . sprintf('%002d',$schedule_form['end_time_minute']) . ' ' . $schedule_form['end_time_period']
                ));

                $providerIrreg = $providerIrreg->fetchAll();

            //REGULAR TYPE
            $providerReg = $this->getEntityManager()->getConnection()->prepare("
                SELECT 
                    pws.`id`,
                    pws.`day_name` AS dayName,
                    CONCAT(pws.`from_hr`, ':', LPAD(pws.`from_min`, 2, 0), ' ', pws.`from_am`) AS fromTime,
                    CONCAT(pws.`to_hr`, ':', LPAD(pws.`to_min`, 2, 0), ' ', pws.`to_am`) AS toTime,
                    hf.`id` AS healthFacilityId,
                    hf.`description` AS healthFacilityDesc
                FROM `provider_work_schedule` pws 
                LEFT JOIN `health_facility` hf ON pws.`health_facility_id` = hf.`id` 
                WHERE pws.`provider_id` = :provider_id
                AND pws.`type` = 'Regular'
                AND pws.`is_deleted` = 0
                AND pws.`day_name` = :dayName
                AND pws.`health_facility_id` = :healthFacility
                AND (
                        STR_TO_DATE(:startTime, '%l:%i %p') BETWEEN STR_TO_DATE(CONCAT(pws.`from_hr`, ':', LPAD(pws.`from_min`, 2,0), ' ', pws.`from_am`), '%l:%i %p') AND STR_TO_DATE(CONCAT(pws.`to_hr`, ':', LPAD(pws.`to_min`, 2,0), ' ', pws.`to_am`), '%l:%i %p')
                        AND STR_TO_DATE(:endTime, '%l:%i %p') BETWEEN STR_TO_DATE(CONCAT(pws.`from_hr`, ':', LPAD(pws.`from_min`, 2,0), ' ', pws.`from_am`), '%l:%i %p') AND STR_TO_DATE(CONCAT(pws.`to_hr`, ':', LPAD(pws.`to_min`, 2,0), ' ', pws.`to_am`), '%l:%i %p')
                        
                   )
                LIMIT 1
            ");
            $providerReg->execute(array(
                'provider_id' => $schedule_form['provider'],
                'dayName' => date('l', strtotime($schedule_form['schedule_date'])),
                'healthFacility' => $schedule_form['health_facility'],
                'startTime' => $schedule_form['start_time_hour'] . ':' . sprintf('%002d',$schedule_form['start_time_minute']) . ' ' . $schedule_form['start_time_period'],
                'endTime' => $schedule_form['end_time_hour'] . ':' . sprintf('%002d',$schedule_form['end_time_minute']) . ' ' . $schedule_form['end_time_period']
            ));
            $providerReg = $providerReg->fetchAll();
            if(!isset($providerIrreg[0])  && !isset($providerReg[0])){
                $errors[] = 'Invalid schedule date and time. Clinician is not available in the selected branch, date and time.';
            }elseif(isset($providerIrreg[0]) && $schedule_form['health_facility'] != $providerIrreg[0]['healthFacilityId']) {
                $errors[] = 'Physician is in ' . $providerIrreg[0]['healthFacilityDesc'] . ' branch';
            }elseif(isset($providerReg[0]) && !isset($providerIrreg[0]) && $schedule_form['health_facility'] != $providerReg[0]['healthFacilityId']){
                $errors[] = 'Physician is in ' . $providerReg[0]['healthFacilityDesc'] . ' branch';
            }

            $scheduleDateExploded = explode('/', $schedule_form['schedule_date']);
            if(!checkdate($scheduleDateExploded[0], $scheduleDateExploded[1], $scheduleDateExploded[2])){
                $errors[] = 'Invalid schedule date format ' . '(' . $schedule_form['schedule_date'] . ').';
            }
        }else{
            if(empty($schedule_form['note'])){
                $errors[] = 'Notes should not be blank.';
            }
        }

        //PATIENT LAST ENCOUNTER BRANCH
//        $lastEncounterBranch = $this->getEntityManager()->getConnection()->prepare("
//                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
//            ");
//        $lastEncounterBranch->execute(array(
//            'patient' => $schedule_form['patient']
//        ));
//
//        $result = $lastEncounterBranch->fetchAll();
//
//        if($result[0] != $schedule_form['health_facility']) {
//            $errors[] = 'The patient last encounter branch is different';
//        }

        return $errors;
    }
    public function ajax_list(array $get){

        $columns = array(
            array('a.`description`', 'a.`description`', 'account_desc'),
            array('s.`id`', "s.`id`"),
            array('hf.`description`', 'hf.`description`', 'health_facility_desc'),
            array('p.`full_name`', 'p.`full_name`', 'patient_fullName'),
            array('s.`schedule_date`', "DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y')", 'schedule_date'),
            array('pr.`full_name`', 'pr.`full_name`', 'provider_fullName'),
            array('ct.`description`', 'ct.`description`', 'chart_type_desc'),
            array('s.`start_time`', "s.`start_time`"),
            array('s.`end_time`', "s.`end_time`"),
            array('ss.`description`', "ss.`description`", 'schedule_status_desc')
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `schedule` s";
        $joins = "LEFT JOIN `patient` p ON s.`patient_id` = p.`id`";
        $joins .= " LEFT JOIN `provider` pr ON s.`provider_id` = pr.`id`";
        $joins .= " LEFT JOIN `chart_type` ct ON s.`chart_type_id` = ct.`id`";
        $joins .= " LEFT JOIN `schedule_status` ss ON s.`schedule_status_id` = ss.`id`";
        $joins .= " LEFT JOIN `health_facility` hf ON s.`health_facility_id` = hf.`id`";
        $joins .= " LEFT JOIN `account` a ON hf.`account_id` = a.`id`";
        $sqlWhere = "WHERE s.`is_deleted` = 0";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array();

        if($get['type'] === 'Timed'){
            $sqlWhere .= "WHERE hf.`schedule_type` IN ('Timed')";
        }else{
            $sqlWhere .= "WHERE hf.`schedule_type` IN ('Queued')";
        }

        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 patient_ajax_list(array $get){

        $columns = array(
            array('s.`id`', "s.`id`"),
            array('hf.`description`', 'hf.`description`', 'health_facility_desc'),
            array('s.`schedule_date`', "DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y')", 'schedule_date'),
            array('pr.`full_name`', 'pr.`full_name`', 'provider_fullName'),
            array('ct.`description`', 'ct.`description`', 'chart_type_desc'),
            array('s.`start_time`', "s.`start_time`"),
            array('s.`end_time`', "s.`end_time`"),
            array('ss.`description`', "ss.`description`", 'schedule_status_desc'),
            array('p.`id`', "p.`id`", 'patient_id'),
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `schedule` s";
        $joins = "LEFT JOIN `patient` p ON s.`patient_id` = p.`id`";
        $joins .= " LEFT JOIN `provider` pr ON s.`provider_id` = pr.`id`";
        $joins .= " LEFT JOIN `chart_type` ct ON s.`chart_type_id` = ct.`id`";
        $joins .= " LEFT JOIN `schedule_status` ss ON s.`schedule_status_id` = ss.`id`";
        $joins .= " LEFT JOIN `health_facility` hf ON s.`health_facility_id` = hf.`id`";
        $joins .= " LEFT JOIN `account` a ON hf.`account_id` = a.`id`";
        $sqlWhere = "WHERE s.`patient_id` = :patient_id";
        $sqlWhere .= " AND (s.`is_deleted` = 0 OR s.`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 command_getPatientsWithAppointment($date){

        $sql = "
            SELECT
              s.`id`,
              s.`start_time` AS startTime,
              s.`end_time` AS endTime,
              DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y') AS scheduleDate,
              cp.`description` AS chartType,
              p.`full_name` AS patient,
              p.`first_name` AS patient_firstName,
              IF(p.`mobile_no` IS NOT NULL, p.`mobile_no`, IF(p.`mobile_no_two` IS NOT NULL, p.`mobile_no_two` , p.mobile_no_three)) as patientMobile,
              IF(p.`phone_no` IS NOT NULL, p.`phone_no`, IF(p.`phone_no_two` IS NOT NULL, p.`phone_no_two` , p.phone_no_three)) as patientPhone,
              pr.`full_name` as provider_fullName,
              prt.`description` as providerType_desc,
              hf.`description` as healthFacility_desc
            FROM `schedule` s
                   LEFT JOIN `chart_type` cp ON s.`chart_type_id` = cp.`id`
                   LEFT JOIN `patient_chart` pc ON s.`id` = pc.`schedule_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 `patient` p ON s.`patient_id` = p.`id`
                   LEFT JOIN `provider` pr on s.`provider_id` = pr.`id`
                   LEFT JOIN `provider_type` prt on pr.`provider_type_id` = prt.`id`
                  LEFT JOIN `health_facility` hf on s.`health_facility_id` = hf.`id`
            WHERE s.`schedule_date` = :date
              AND s.`is_deleted` = 0
              AND ss.`description` NOT IN ('Cancelled', 'Completed')
        ";

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

        $query->execute(array(
            'date' => $date
        ));

        $result = $query->fetchAll();

        return $result;
    }

    // REPORT

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

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

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

        if(isset($params['clinician']) && !empty($params['clinician']) && 'NULL' !== strtoupper($params['clinician'])){
            $clinicians = explode(',' , $params['clinician']);
            if(count($clinicians) > 0){
                $co_ors = '';
                foreach($clinicians as $key => $clinicianId){
                    $co_ors .= (empty($co_ors) ? '' : ' OR ') . "s.`provider_id` = :clinician_id_$key";
                    $stmtParams["clinician_id_$key"] = $clinicianId;
                }
                $sqlWhere .= " AND ($co_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['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 ') . "s.`health_facility_id` = :healthFacility_id_$key";
                    $stmtParams["healthFacility_id_$key"] = $branchId;
                }
                $sqlWhere .= " AND ($hf_ors) ";
            }
        }

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

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

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

        $sql = "
            SELECT
                s.`id` AS scheduleId,
                s.`start_time` AS startTime,
                s.`end_time` AS endTime,
                co.`description` AS companyDesc,
                s.`is_deleted` AS isDeleted,
                DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y') AS scheduleDate,
                cp.`description` AS chartType,
                ss.`description` AS scheduleStatus,
                cp.`color` AS color,
                p.`full_name` AS patient,
                s.`patient_id` AS patientId,
                prov.`full_name` AS clinician,
                hf.`description` AS branch,
                cs.`description` AS chartStatus,
                au.`full_name` AS user
            FROM `schedule` s
            LEFT JOIN `chart_type` cp ON s.`chart_type_id` = cp.`id`
            LEFT JOIN `patient_chart` pc ON s.`id` = pc.`schedule_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` prov ON prov.`id` = s.`provider_id`
            LEFT JOIN `health_facility` hf ON hf.`id` = s.`health_facility_id`
            LEFT JOIN `company` co ON hf.`company_id` = co.`id`
            LEFT JOIN `patient` p ON s.`patient_id` = p.`id`
            LEFT JOIN `account_user` au ON au.`username` = s.`created_by`
            $sqlWhere
            AND s.`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
            )
            AND p.`is_active` = 1
            ORDER BY s.`schedule_date` DESC
        ";

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

        $result = $query->fetchAll();

        return $result;

    }

    public function scheduleCalendarViewSchedules($healthFacility_id, $date) {

        $sql = "
            SELECT
                s.`id`,
                s.`start_time` AS startTime,
                s.`end_time` AS endTime,
                s.`is_deleted` AS isDeleted,
                DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y') AS scheduleDate,
                CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), s.`start_time`) AS scheduleDT,
                CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), s.`end_time`) AS scheduleDTEnd,
                cp.`description` AS chartType,
                ss.`description` AS scheduleStatus,
                cp.`color` AS color,
                p.`full_name` AS patient,
                s.`patient_id`,
                s.`provider_id` AS providerId,
                cs.`description` AS chartStatus
            FROM `schedule` s
            LEFT JOIN `chart_type` cp ON s.`chart_type_id` = cp.`id`
            LEFT JOIN `patient_chart` pc ON s.`id` = pc.`schedule_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 `patient` p ON s.`patient_id` = p.`id`
            WHERE s.`schedule_date` = :date
            AND s.`is_deleted` = 0
            AND s.`provider_id` IN (
                SELECT
                    p.`id`
                FROM `provider_health_facility` phf
                LEFT JOIN `provider` p ON phf.`provider_id` = p.`id`
                WHERE phf.`health_facility_id` = :healthFacility_id
                AND phf.`is_deleted` = 0
                -- AND phf.`start_date` <= :date 
                -- AND (phf.`end_date` IS NULL OR phf.`end_date` >= :date)
            )
            AND s.`health_facility_id` = :healthFacility_id
        ";

        $query = $this->getEntityManager()->getConnection()->prepare($sql);
        $query->execute(array(
            'healthFacility_id' => $healthFacility_id,
            'date' => date('Y-m-d 00:00:00', strtotime($date))
        ));

        $result = $query->fetchAll();

        return $result;
    }

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

    public function rx_schedule_calendar_view_schedules_by_provider($healthFacility_id, $date, $providerId) {

        $sql = "
            SELECT
                s.`id`,
                s.`start_time` AS startTime,
                s.`end_time` AS endTime,
                s.`is_deleted` AS isDeleted,
                DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y') AS scheduleDate,
                CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), s.`start_time`) AS scheduleDT,
                CONCAT_WS(' ', DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y'), s.`end_time`) AS scheduleDTEnd,
                cp.`description` AS chartType,
                ss.`description` AS scheduleStatus,
                ss.`id` AS scheduleStatusId,
                cp.`color` AS color,
                p.`full_name` AS patient,
                s.`patient_id`,
                s.`provider_id` AS providerId,
                s.`note`,
                cs.`description` AS chartStatus
            FROM `schedule` s
            LEFT JOIN `chart_type` cp ON s.`chart_type_id` = cp.`id`
            LEFT JOIN `patient_chart` pc ON s.`id` = pc.`schedule_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 `patient` p ON s.`patient_id` = p.`id`
            WHERE s.`schedule_date` = :date
            AND s.`provider_id` = :provider_id
            AND s.`is_deleted` = 0
            AND s.`provider_id` IN (
                SELECT
                    p.`id`
                FROM `provider_health_facility` phf
                LEFT JOIN `provider` p ON phf.`provider_id` = p.`id`
                WHERE phf.`health_facility_id` = :healthFacility_id
                AND phf.`is_deleted` = 0
                -- AND phf.`start_date` <= :date 
                -- AND (phf.`end_date` IS NULL OR phf.`end_date` >= :date)
            )
            AND s.`health_facility_id` = :healthFacility_id
        ";

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

        $query->execute(array(
            'healthFacility_id' => $healthFacility_id,
            'date' => date('Y-m-d 00:00:00', strtotime($date)),
            'provider_id' => $providerId ,
        ));

        $result = $query->fetchAll();

        return $result;
    }

    public function management_dashboard_booked_count_daily($params) {
        $sqlWhere = 'WHERE s.`is_deleted` = 0
                     AND hf.`is_deleted` = 0';
        $sqlWhere .= " AND s.`schedule_date` = :dateFrom";
        $stmtParams = array();

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

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

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

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

        $sql1 = "
            SELECT
                hf.`id` AS health_facility_id,
                hf.`description`
            FROM `health_facility` hf
            WHERE hf.`is_deleted` = 0
            AND hf.`description` IS NOT NULL   
            AND hf.`description` NOT IN('Demo_Test', 'DemoLocation') 
        ";

        $sql2 = "
            SELECT 
                s.`health_facility_id`,
                SUM(IF(ct.`description` = 'HSAT', 1, 0)) AS hsatCount,
                SUM(IF(ct.`description` = '6 Month Follow-up', 1, 0)) AS sixMonthsFF
            FROM `schedule` s
            LEFT JOIN `health_facility` hf ON s.`health_facility_id` = hf.`id`
            LEFT JOIN `chart_type` ct ON s.`chart_type_id` = ct.`id`
            $sqlWhere
            AND hf.`description` IS NOT NULL
            AND hf.`description` NOT IN('Demo_Test', 'DemoLocation') 
            GROUP BY s.`health_facility_id`
        ";

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

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

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

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

        $healthFacilities = array();

        foreach($result1 as $key1 => $hf) {
            $bookedCount = array();
            foreach ($result2 as $key2 => $record) {
                if ($hf['health_facility_id'] == $record['health_facility_id']) {
                    $bookedCount[] = array(
                        'health_facility_id' => $record['health_facility_id'],
                        'booked_count' => array(
                            'health_facility_id' => $record['health_facility_id'],
                            'hsat_count' => $record['hsatCount'],
                            'six_month_ff_count' => $record['sixMonthsFF'],
                        )
                    );
                }
            }

            if (count($bookedCount)) {
                $healthFacilities[] = array(
                    'description' => $hf['description'],
                    'booked' => $bookedCount,
                );
            }
        }

        $result['dashboard'] = '';
        $result['appointment'] = $healthFacilities;

        return $result;
    }

    public function management_dashboard_booked_count_monthly($params) {
        $sqlWhere = 'WHERE s.`is_deleted` = 0
                     AND hf.`is_deleted` = 0';
        $sqlWhere .= " AND s.`schedule_date` BETWEEN :dateFrom AND :dateTo";
        $stmtParams = array();

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

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

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

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

        $sql1 = "
            SELECT
                hf.`id` AS health_facility_id,
                hf.`description`
            FROM `health_facility` hf
            WHERE hf.`is_deleted` = 0
            AND hf.`description` IS NOT NULL   
            AND hf.`description` NOT IN('Demo_Test', 'DemoLocation') 
        ";

        $sql2 = "
            SELECT 
                s.`health_facility_id`,
                SUM(IF(ct.`description` = 'HSAT', 1, 0)) AS hsatCount,
                SUM(IF(ct.`description` = '6 Month Follow-up', 1, 0)) AS sixMonthsFF
            FROM `schedule` s
            LEFT JOIN `health_facility` hf ON s.`health_facility_id` = hf.`id`
            LEFT JOIN `chart_type` ct ON s.`chart_type_id` = ct.`id`
            $sqlWhere
            AND hf.`description` IS NOT NULL
            AND hf.`description` NOT IN('Demo_Test', 'DemoLocation') 
            GROUP BY s.`health_facility_id`
        ";

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

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

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

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

        $healthFacilities = array();

        foreach($result1 as $key1 => $hf) {
            $bookedCount = array();
            foreach ($result2 as $key2 => $record) {
                if ($hf['health_facility_id'] == $record['health_facility_id']) {
                    $bookedCount[] = array(
                        'health_facility_id' => $record['health_facility_id'],
                        'booked_count' => array(
                            'health_facility_id' => $record['health_facility_id'],
                            'hsat_count' => $record['hsatCount'],
                            'six_month_ff_count' => $record['sixMonthsFF'],
                        )
                    );
                }
            }

            if (count($bookedCount)) {
                $healthFacilities[] = array(
                    'description' => $hf['description'],
                    'booked' => $bookedCount,
                );
            }
        }

        $result['dashboard'] = '';
        $result['appointment'] = $healthFacilities;

        return $result;
    }

    //ZAPSYS
    public function rx_schedule_calendar_list($healthFacility_id, $date, $providerId) {
        $sqlWhere = 'WHERE s.`is_deleted` = 0';
        $stmtParams = array();
        if(isset($date) && !empty($date) && 'NULL' !== $date){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "s.`schedule_date` = :date";
            $stmtParams['date'] = $date;
        }
        if(isset($providerId) && !empty($providerId) && 'NULL' !== $providerId){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "s.`provider_id` = :provider_id";
            $stmtParams['provider_id'] = $providerId;
        }
        if(isset($healthFacility_id) && !empty($healthFacility_id) && 'NULL' !== $healthFacility_id){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") .
                "s.`provider_id` IN (
                SELECT
                    p.`id`
                FROM `provider_health_facility` phf
                LEFT JOIN `provider` p ON phf.`provider_id` = p.`id`
                WHERE phf.`health_facility_id` = :healthFacility_id
                AND phf.`is_deleted` = 0
            )";
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "s.`health_facility_id` = :healthFacility_id";
            $stmtParams['healthFacility_id'] = $healthFacility_id;
        }

        $sql = "
            SELECT
                s.`id` AS scheduleId,
                s.`start_time` AS startTime,
                s.`end_time` AS endTime,
                DATE_FORMAT(s.`schedule_date`, '%m/%d/%Y') AS scheduleDate,
                s.`chart_type_id` AS chartTypeId,
                ct.`description` AS chartType,
                ct.`color` AS color,
                s.`patient_id` AS patientId
                p.`full_name` AS patient,
                ss.`description` AS scheduleStatus,
                s.`schedule_status_id` AS scheduleStatusId,
                s.`note`,
                s.`provider_id` AS providerId,
                pr.`full_name` AS provider
            FROM `schedule` s
            LEFT JOIN `chart_type` ct ON s.`chart_type_id` = ct.`id`
            LEFT JOIN `patient_chart` pc ON s.`id` = pc.`schedule_id`
            LEFT JOIN `schedule_status` ss ON s.`schedule_status_id` = ss.`id`
            LEFT JOIN `patient` p ON s.`patient_id` = p.`id`
            LEFT JOIN `provider` pr ON s.`provider_id` = pr.`id`
            $sqlWhere
        ";

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

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }
}
