<?php

namespace RxConnexionBundle\Repository;

/**
 * MarketingStaffScheduleRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class MarketingStaffScheduleRepository 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:MarketingStaffScheduleEntity')
                ->createQueryBuilder('s')
                ->leftJoin('s.marketingStaffScheduleStatus', 'ss')
                ->where('s.id != :id')
                ->andwhere('s.healthFacility = :healthFacility_id')
                ->andWhere('s.marketingStaff = :marketing_staff_id')
                ->andWhere('s.scheduleDate = :scheduleDate')
                ->andWhere('ss.description != :scheduleStatus')
                ->andWhere('ss.description != :scheduleStatus2')
                ->andWhere('(s.isDeleted IS NULL OR s.isDeleted != 1)')
                ->setParameter('id', $schedule_form['id'])
                ->setParameter('healthFacility_id', $schedule_form['health_facility'])
                ->setParameter('marketing_staff_id', $schedule_form['marketingStaff'])
                ->setParameter('scheduleDate', date('Y-m-d', strtotime($schedule_form['schedule_date'])) . ' 00:00:00')
                ->setParameter('scheduleStatus', 'Cancelled')
                ->setParameter('scheduleStatus2', 'No Show')
                ->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, marketing staff, 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['marketingStaffScheduleStatus'])){
                $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

            $marketingStaffOut = $this->getEntityManager()->getConnection()->prepare("
                SELECT `id`
                FROM `marketing_staff_work_schedule` pws 
                WHERE pws.`marketing_staff_id` = :marketing_staff_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
            ");
            $marketingStaffOut->execute(array(
                'marketing_staff_id' => $schedule_form['marketingStaff'],
                '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']
            ));
            $marketingStaffOut = $marketingStaffOut->fetchAll();
            if(isset($marketingStaffOut[0])) {
                $errors[] = 'Marketing Staff is out.';
            }

            //IRREGULAR TYPE

            $marketingStaffIrreg = $this->getEntityManager()->getConnection()->prepare("
                SELECT 
                    pws.`id`,
                    hf.`description` AS healthFacilityDesc,
                    hf.`id` AS healthFacilityId
                FROM `marketing_staff_work_schedule` pws
                LEFT JOIN `health_facility` hf ON pws.`health_facility_id` = hf.`id`
                WHERE pws.`marketing_staff_id` = :marketing_staff_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
            ");
            $marketingStaffIrreg->execute(array(
                'marketing_staff_id' => $schedule_form['marketingStaff'],
                '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']
            ));

            $marketingStaffIrreg = $marketingStaffIrreg->fetchAll();

            //REGULAR TYPE
            $marketingStaffReg = $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 `marketing_staff_work_schedule` pws 
                LEFT JOIN `health_facility` hf ON pws.`health_facility_id` = hf.`id` 
                WHERE pws.`marketing_staff_id` = :marketing_staff_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
            ");
            $marketingStaffReg->execute(array(
                'marketing_staff_id' => $schedule_form['marketingStaff'],
                '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']
            ));
            $marketingStaffReg = $marketingStaffReg->fetchAll();

            if(!isset($marketingStaffIrreg[0])  && !isset($marketingStaffReg[0])){
                $errors[] = 'Invalid schedule date and time. Marketing Staff is not available in the selected branch, date and time.';
            }elseif(isset($marketingStaffIrreg[0]) && $schedule_form['health_facility'] != $marketingStaffIrreg[0]['healthFacilityId']) {
                $errors[] = 'Marketing Staff is in ' . $marketingStaffIrreg[0]['healthFacilityDesc'] . ' branch';
            }elseif(isset($marketingStaffReg[0]) && !isset($marketingStaffIrreg[0]) && $schedule_form['health_facility'] != $marketingStaffReg[0]['healthFacilityId']){
                $errors[] = 'Marketing Staff is in ' . $marketingStaffReg[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`', 'marketing_staff_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 `marketing_staff` pr ON s.`marketing_staff_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 referring_provider_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`', 'marketingStaff_desc'),
            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`", 'referring_provider_id'),
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `marketing_staff_schedule` s";
        $joins = "LEFT JOIN `referring_provider` p ON s.`referring_provider_id` = p.`id`";
        $joins .= " LEFT JOIN `marketing_staff` pr ON s.`marketing_staff_id` = pr.`id`";
        $joins .= " LEFT JOIN `chart_type` ct ON s.`chart_type_id` = ct.`id`";
        $joins .= " LEFT JOIN `marketing_staff_schedule_status` ss ON s.`marketing_staff_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.`referring_provider_id` = :referring_provider_id";
        $sqlWhere .= " AND (s.`is_deleted` = 0 OR s.`is_deleted` IS NULL)";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array(
            'referring_provider_id' => base64_decode($get['referring_provider_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 referring_health_facility_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`', 'marketingStaff_desc'),
            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`", 'referring_health_facility_id'),
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `marketing_staff_schedule` s";
        $joins = "LEFT JOIN `referring_health_facility` p ON s.`referring_health_facility_id` = p.`id`";
        $joins .= " LEFT JOIN `marketing_staff` pr ON s.`marketing_staff_id` = pr.`id`";
        $joins .= " LEFT JOIN `chart_type` ct ON s.`chart_type_id` = ct.`id`";
        $joins .= " LEFT JOIN `marketing_staff_schedule_status` ss ON s.`marketing_staff_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.`referring_health_facility_id` = :referring_health_facility_id";
        $sqlWhere .= " AND (s.`is_deleted` = 0 OR s.`is_deleted` IS NULL)";
        $groupBy = "";
        $orderBy = "";
        $limit = "";
        $stmtParams = array(
            'referring_health_facility_id' => base64_decode($get['referring_health_facility_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 marketingStaff_fullName,
              prt.`description` as marketingStaffType_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 `marketing_staff` pr on s.`marketing_staff_id` = pr.`id`
                   LEFT JOIN `marketing_staff_type` prt on pr.`marketing_staff_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;
    }

    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,
                rhf.`description` AS referringHealthFacilityDesc,
                rp.`full_name` AS referringProviderFullName,
                s.`referring_health_facility_id`,
                s.`referring_provider_id`,
                s.`marketing_staff_id` AS marketingStaffId,
                rhcs.`description` AS rhfChartStatus,
                rpcs.`description` AS rpChartStatus
            FROM `marketing_staff_schedule` s
            LEFT JOIN `chart_type` cp ON s.`chart_type_id` = cp.`id`
            LEFT JOIN `referring_health_facility_chart` rhfc ON s.`id` = rhfc.`marketing_staff_schedule_id`
            LEFT JOIN `referring_provider_chart` rpc ON s.`id` = rpc.`marketing_staff_schedule_id`
            LEFT JOIN `chart_status` rhcs ON rhfc.`chart_status_id` = rhcs.`id`
            LEFT JOIN `chart_status` rpcs ON rpc.`chart_status_id` = rpcs.`id`
            LEFT JOIN `marketing_staff_schedule_status` ss ON s.`marketing_staff_schedule_status_id` = ss.`id`
            LEFT JOIN `referring_health_facility` rhf ON s.`referring_health_facility_id` = rhf.`id`
            LEFT JOIN `referring_provider` rp ON s.`referring_provider_id` = rp.`id`
            WHERE s.`schedule_date` = :date
            AND s.`is_deleted` = 0
            AND s.`marketing_staff_id` IN (
                SELECT
                    p.`id`
                FROM `marketing_staff_health_facility` phf
                LEFT JOIN `marketing_staff` p ON phf.`marketing_staff_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_marketing_staff($healthFacility_id, $date, $marketingStaff_Id) {

        $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.`marketing_staff_id` AS marketingStaffId,
                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.`marketing_staff_id` = :marketing_staff_id
            AND s.`is_deleted` = 0
            AND s.`marketing_staff_id` IN (
                SELECT
                    p.`id`
                FROM `marketing_staff_health_facility` phf
                LEFT JOIN `marketing_staff` p ON phf.`marketing_staff_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)),
            'marketing_staff_id' => $marketingStaff_Id ,
        ));

        $result = $query->fetchAll();

        return $result;
    }

    //ZAPSYS
    public function rx_schedule_calendar_list($healthFacility_id, $date, $marketingStaff_Id) {
        $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($marketingStaff_Id) && !empty($marketingStaff_Id) && 'NULL' !== $marketingStaff_Id){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") . "s.`marketing_staff_id` = :marketing_staff_id";
            $stmtParams['marketing_staff_id'] = $marketingStaff_Id;
        }
        if(isset($healthFacility_id) && !empty($healthFacility_id) && 'NULL' !== $healthFacility_id){
            $sqlWhere .= (empty($sqlWhere) ? "WHERE " : " AND ") .
                "s.`marketing_staff_id` IN (
                SELECT
                    p.`id`
                FROM `marketing_staff_health_facility` phf
                LEFT JOIN `marketing_staff` p ON phf.`marketing_staff_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.`marketing_staff_id` AS marketingStaffId,
                pr.`full_name` AS marketing_staff
            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 `marketing_staff` pr ON s.`marketing_staff_id` = pr.`id`
            $sqlWhere
        ";

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

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }
}
