<?php

namespace RxConnexionBundle\Repository;

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

    public function validate($healthFacility_form) {

        $errors = array();

        /*$action = $healthFacility_form['action'];

        // d = delete
        if($action !== 'd') {

            $healthFacilityExist = $this->getEntityManager()->getRepository('RxConnexionBundle:HealthFacilityEntity')
                ->createQueryBuilder('ep')
                ->where('ep.id != :id')
                ->andWhere('ep.description = :description')
                ->andWhere('ep.account = :account')
                ->setParameters(array(
                    'id' => $healthFacility_form['id'],
                    'description' => $healthFacility_form['description'],
                    'account' => $healthFacility_form['account']
                ))
                ->getQuery()->getResult();

            if($healthFacilityExist)
                $errors[] = 'Description already exist.';

            if(empty($healthFacility_form['description']))
                $errors[] = 'Description should not be blank.';
        }*/

        return $errors;
    }

    public function ajax_list(array $get){

        $columns = array(
            array('hf.`id`', "hf.`id`"),
            array('c.`description`', 'c.`description`', 'company_description'),
            array('hf.`description`', 'hf.`description`'),
            array('hf.`address`', 'hf.`address`'),
            array('hf.`schedule_type`', 'hf.`schedule_type`'),
            array('hf.`mobile_no`', 'hf.`mobile_no`'),
            array('hf.`phone_no`', 'hf.`phone_no`'),
            array('hf.`email`', 'hf.`email`'),
        );
        $asColumns = array();

        $select = "SELECT";
        $from = "FROM `health_facility` hf";
        $joins = "LEFT JOIN `health_facility_type` hft ON hf.`health_facility_type_id` = hft.`id`";
        $joins .= " LEFT JOIN `company` c ON hf.`company_id` = c.`id`";
        $sqlWhere = "WHERE hf.`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'] . "%";
            }
        }
        if($get['status']){
            if($get['status'] == 'not_active') {
                $status = 0;
            } else {
                $status = 1;
            }
            $sqlWhere .= (!empty($sqlWhere) ? ' AND ' : 'WHERE ') . '`is_active` = :status';
            $stmtParams['status'] = $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)
        );
    }

    public function reportMedicalInstitution($params) {
        $sqlWhere = "WHERE rhfp.`is_deleted` = 0";
        $stmtParams = array();

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

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

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

        $sql = "
            SELECT 
                rhf.`id` AS affiliateId,  
                rhf.`description` AS affiliate,  
                rp.`full_name` AS referring_affiliate,
                rhf.`address` AS address,
                rhf.`mobile_no` AS mobile_no, 
                rhf.`mobile_no_two` AS mobile_no_two, 
                rhf.`phone_no` AS phone_no,  
                rhf.`phone_no_two` AS phone_no_two,
                rhf.`email` AS email,
                temp_sarhf.salesAgent
            FROM `referring_health_facility_provider` rhfp
            LEFT JOIN `referring_health_facility` rhf ON rhf.`id` = rhfp.`referring_health_facility_id`
            LEFT JOIN `referring_provider` rp ON rp.`id` = rhfp.`referring_provider_id`
            LEFT JOIN(SELECT 
            				sarhf.`referring_health_facility_id`,
            				sa.`id` salesAgentId,
            				sa.`full_name` salesAgent
            			 FROM `sales_agent_referring_health_facility` sarhf
            			 LEFT JOIN `referring_health_facility` rhf ON rhf.`id` = sarhf.`referring_health_facility_id`
            			 LEFT JOIN `sales_agent` sa ON sa.`id` = sarhf.`sales_agent_id`
            			 WHERE sa.`is_active` = 1
            			 AND sa.`is_deleted` = 0
                      ) temp_sarhf ON temp_sarhf.`referring_health_facility_id` = rhf.`id` 
            $sqlWhere
        ";

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

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    public function autocomplete_suggestions($q, $userData = null, $allActive = false) {

        $andWhere = "";
        $stmtParams = array(
            'description' => "%$q%"
        );

        if($allActive){
	        $andWhere .= (empty($andWhere) ? " AND " : "") . " hf.is_deleted = 0 AND hf.is_active = 1 ";
        } else {
	        if(!is_null($userData) && !empty($userData)){
		        if($userData['type'] != 'Super Admin'){
			        if(count($userData['branches']) > 0){
				        $hf_ors = '';
				        foreach($userData['branches'] as $key => $branchId){
					        $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "hf.`id` = :healthFacility_id_$key";
					        $stmtParams["healthFacility_id_$key"] = $branchId;
				        }
				        $andWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors) ";
			        } else {
				        $andWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
			        }
		        }
	        }
        }

        $sql = "
            SELECT
                hf.`id`,
                hf.`city_id`,
                hf.`address`,
                CONCAT_WS(', ', hf.`mobile_no`, hf.`mobile_no_two`, hf.`mobile_no_three`) AS mobileNos,
                CONCAT_WS(', ', hf.`phone_no`, hf.`phone_no_two`, hf.`phone_no_three`) AS phoneNos,
                hf.`geo_latitude` AS geoLat,
                hf.`geo_longitude` AS geoLong,
                CONCAT_WS(' - ', hf.`description`, hft.`description`) AS data,
                CONCAT_WS(' - ', hf.`description`, hft.`description`) AS value
            FROM `health_facility` hf
            LEFT JOIN `health_facility_type` hft ON hf.`health_facility_type_id` = hft.`id`
            WHERE CONCAT_WS(' - ', hf.`description`, hft.`description`) LIKE :description
            AND hf.`is_active` = 1
            AND hf.`is_deleted` = 0
            $andWhere
            ORDER BY hf.`description`
            LIMIT 0,20
        ";

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

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    public function schedule_healthFacility_autocomplete_suggestions($q, $provider_id) {
        $andWhere = '';
        $params = array(
            'description' => "%$q%"
        );

        if(isset($provider_id) && $provider_id != ''){
            $andWhere .= ' AND hf.`id` IN (
                           SELECT phf.`health_facility_id`
                           FROM `provider_health_facility` phf
                           WHERE phf.`provider_id` = :provider_id
                           AND phf.`is_deleted` = 0
                        )';
            $params['provider_id'] = $provider_id;
        }
        $sql = "
            SELECT
                hf.`id`,
                CONCAT_WS(' - ', hf.`description`, hft.`description`) AS data,
                CONCAT_WS(' - ', hf.`description`, hft.`description`) AS value
            FROM `health_facility` hf
            LEFT JOIN `health_facility_type` hft ON hf.`health_facility_type_id` = hft.`id`
            WHERE CONCAT_WS(' - ', hf.`description`, hft.`description`) LIKE :description
            AND hf.`is_active` = 1
            AND hf.`is_deleted` = 0
            $andWhere
            ORDER BY hf.`description`
            LIMIT 0,20
        ";

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

        $query->execute($params);

        $result = $query->fetchAll();

        return $result;
    }

    public function billing_healthFacility_autocomplete_suggestions($q) {
        $andWhere = '';
        $params = array(
            'description' => "%$q%"
        );
        $sql = "
            SELECT
                hf.`id`,
                CONCAT_WS(' - ', hf.`description`, hf.`address`) AS data,
                CONCAT_WS(' - ', hf.`description`, hf.`address`) AS value
            FROM `health_facility` hf
            WHERE CONCAT_WS(' - ', hf.`description`, hf.`address`) LIKE :description
            AND hf.`is_active` = 1
            AND hf.`is_deleted` = 0
            $andWhere
            ORDER BY hf.`description`
            LIMIT 0,20
        ";

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

        $query->execute($params);

        $result = $query->fetchAll();

        return $result;
    }

    public function healthFacilityForMap() {

        $sql = "
            SELECT
            hf.`address`,
            hf.`description`,
            hf.`geo_latitude` AS geoLat,
            hf.`geo_longitude` AS geoLong
            FROM `health_facility` hf
            WHERE hf.geo_latitude IS NOT NULL
            AND hf.geo_longitude IS NOT NULL
            AND hf.`is_deleted` = 0
            AND hf.`is_active` = 1
        ";

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

        $query->execute();

        $result = $query->fetchAll();

        return $result;
    }

    public function findAllNotDeleted()
    {
        $qb = $this->createQueryBuilder('hf');
        $qb->where('hf.isDeleted != :isDeleted')
            ->setParameter('isDeleted', true)
            ->orderBy('hf.description');

        return $qb->getQuery()
            ->getResult();
    }

    public function findAllByIds($ids, $notDeleted = false)
    {
        $qb = $this->createQueryBuilder('hf');
        $qb->where('hf.id IN (:ids) ')
            ->setParameter(':ids', $ids)
            ->orderBy('hf.description');

        if($notDeleted) {
	        $qb->andWhere('hf.isDeleted != :isDeleted')
		        ->setParameter('isDeleted', true);
        }

        return $qb->getQuery()
            ->getResult();
    }

    public function findAllNotDeletedAndHasPatient($userData = null)
    {
        $andWhere = '';
        $stmtParams = array();
        if(!is_null($userData) && !empty($userData)){
            if($userData['type'] != 'Super Admin'){
                if(count($userData['company']['companyBranches']) > 0){
                    $hf_ors = '';
                    foreach($userData['company']['companyBranches'] as $key => $branchId){
                        $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "hf.`id` = :healthFacility_id_$key";
                        $stmtParams["healthFacility_id_$key"] = $branchId;
                    }
                    $andWhere .= " AND ($hf_ors) ";
                } else {
                    $andWhere .= " AND (1 != 1) ";
                }
            }
        }
        /*$sql = "
            SELECT hf.`id`,
              hf.`description` ,
              hf.`geo_latitude`,
              hf.`geo_longitude`
            FROM `health_facility` hf
            WHERE  (
              SELECT COUNT(p.`id`) as cnt
              FROM `patient` p
              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.`start_date` <= :now
                    AND (phf2.`end_date` >= :now OR phf2.`end_date` IS NULL)
                    LIMIT 1
                )
              WHERE phf.`health_facility_id` = hf.`id`
              ) > 0
            AND hf.`is_deleted` != 1
            $sqlWhere
            ORDER BY hf.`description`
        ";*/

        $sql = "SELECT *
				FROM health_facility hf
				WHERE hf.`is_deleted` != 1
				  AND hf.id IN (
				  SELECT phf.health_facility_id
				  FROM patient_health_facility phf
				  WHERE phf.`start_date` <= :now AND (phf.`end_date` >= :now OR phf.`end_date` IS NULL)
				)
				$andWhere
				ORDER BY hf.description ASC";
        $stmtParams['now'] = date('Y-m-d 00:00:00');
        $query = $this->getEntityManager()->getConnection()->prepare($sql);

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }
    public function findAllNotDeletedAndHasPatient__old($userData = null)
    {
        $sqlWhere = '';
        $stmtParams = array();
        if(!is_null($userData) && !empty($userData)){
            if($userData['type'] != 'Super Admin'){
                if(count($userData['company']['companyBranches']) > 0){
                    $hf_ors = '';
                    foreach($userData['company']['companyBranches'] as $key => $branchId){
                        $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "hf.`id` = :healthFacility_id_$key";
                        $stmtParams["healthFacility_id_$key"] = $branchId;
                    }
                    $sqlWhere .= (empty($sqlWhere) ? " AND " : "") . " ($hf_ors) ";
                } else {
                    $sqlWhere .= (empty($sqlWhere) ? " AND " : "") . " (1 != 1) ";
                }
            }
        }
        /*$sql = "
            SELECT hf.`id`,
              hf.`description` ,
              hf.`geo_latitude`,
              hf.`geo_longitude`
            FROM `health_facility` hf
            WHERE  (
              SELECT COUNT(p.`id`) as cnt
              FROM `patient` p
              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.`start_date` <= :now
                    AND (phf2.`end_date` >= :now OR phf2.`end_date` IS NULL)
                    LIMIT 1
                )
              WHERE phf.`health_facility_id` = hf.`id`
              ) > 0
            AND hf.`is_deleted` != 1
            $sqlWhere
            ORDER BY hf.`description`
        ";*/

        $sql = "SELECT  hf.`id`,
                        hf.`description` ,
                        hf.`geo_latitude`,
                        hf.`geo_longitude`
                FROM `patient_health_facility` phf
                       LEFT JOIN health_facility `hf` on phf.`health_facility_id` = hf.`id`
                WHERE hf.`is_deleted` != 1
                  AND phf.`start_date` <= :now
                  AND (phf.`end_date` >= :now OR phf.`end_date` IS NULL)
                  $sqlWhere
                GROUP BY phf.`health_facility_id`;";
        $stmtParams['now'] = date('Y-m-d 00:00:00');
        $query = $this->getEntityManager()->getConnection()->prepare($sql);

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    public function getHealthFacilityByCompanyId_toArray($companies) {
        $result = array();
        $stmtParams =array();
        $andWhere = '';
        $ors = '';
        foreach($companies as $key => $company){
            $ors .= (empty($ors) ? '' : ' OR ') . "hf.`company_id` = :company_id$key";
            $stmtParams["company_id$key"] = $company;
        }
        if(!empty($ors)){
            $andWhere .= " AND ($ors) ";
        } else {
            $andWhere .= " AND (1 != 1) ";
        }

        $sql = "
                SELECT
                    hf.`id`
                FROM `health_facility` hf
                WHERE hf.`is_deleted` = 0
                $andWhere
                ORDER BY hf.`description`
            ";

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

        $result = $query->fetchAll();

        $data = array();
        foreach ($result as $r) {
            $data[] = $r['id'];
        }

        return $data;
    }


    public function branchesForMultiple($userData = null) {

        $andWhere = "";
        $stmtParams = array();

        if(!is_null($userData) && !empty($userData)) {
            if ($userData['type'] != 'Super Admin') {
                if (count($userData['company']['companyBranches']) > 0) {
                    $hf_ors = '';
                    foreach ($userData['company']['companyBranches'] as $key => $branchId) {
                        $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "hf.`id` = :healthFacility_id_$key";
                        $stmtParams["healthFacility_id_$key"] = $branchId;
                    }
                    $andWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors) ";
                } else {
                    $andWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
                }
            }
        }

        $sql = "
            SELECT
                hf.`id`,
                hf.`city_id`,
                hf.`address`,
                CONCAT_WS(', ', hf.`mobile_no`, hf.`mobile_no_two`, hf.`mobile_no_three`) AS mobileNos,
                CONCAT_WS(', ', hf.`phone_no`, hf.`phone_no_two`, hf.`phone_no_three`) AS phoneNos,
                hf.`geo_latitude` AS geoLat,
                hf.`geo_longitude` AS geoLong,
                hf.`description`
            FROM `health_facility` hf
            LEFT JOIN `health_facility_type` hft ON hf.`health_facility_type_id` = hft.`id`
            -- WHERE hf.`is_active` = 1
            WHERE hf.`is_deleted` = 0
            $andWhere
            ORDER BY hf.`description`
        ";

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

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

    public function branchesForBillingMultiple($userData = null) {

        $andWhere = "";
        $stmtParams = array();

        if(!is_null($userData) && !empty($userData)) {
            if ($userData['type'] != 'Super Admin') {
                if (count($userData['company']['companyBranches']) > 0) {
                    $hf_ors = '';
                    foreach ($userData['company']['companyBranches'] as $key => $branchId) {
                        $hf_ors .= (empty($hf_ors) ? '' : ' OR ') . "hf.`id` = :healthFacility_id_$key";
                        $stmtParams["healthFacility_id_$key"] = $branchId;
                    }
                    $andWhere .= (empty($andWhere) ? " AND " : "") . " ($hf_ors) ";
                } else {
                    $andWhere .= (empty($andWhere) ? " AND " : "") . " (1 != 1) ";
                }
            }
        }

        $sql = "
            SELECT
                hf.`id`,
                hf.`city_id`,
                hf.`address`,
                CONCAT_WS(', ', hf.`mobile_no`, hf.`mobile_no_two`, hf.`mobile_no_three`) AS mobileNos,
                CONCAT_WS(', ', hf.`phone_no`, hf.`phone_no_two`, hf.`phone_no_three`) AS phoneNos,
                hf.`geo_latitude` AS geoLat,
                hf.`geo_longitude` AS geoLong,
                hf.`description`
            FROM `health_facility` hf
            LEFT JOIN `health_facility_type` hft ON hf.`health_facility_type_id` = hft.`id`
            -- WHERE hf.`is_active` = 1
            WHERE hf.`is_deleted` = 0
            $andWhere
            ORDER BY hf.`description`
        ";

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

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }

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

    public function rx_get_all_active(){

        $sql = "
            SELECT
            hf.`id`,
            hf.`address`,
            hf.`description`,
            hf.`geo_latitude` AS geoLat,
            hf.`geo_longitude` AS geoLong
            FROM `health_facility` hf
            WHERE hf.geo_latitude IS NOT NULL
            AND hf.geo_longitude IS NOT NULL
            AND hf.`is_deleted` = 0
            AND hf.`is_active` = 1
        ";

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

        $query->execute();

        $result = $query->fetchAll();

        return $result;
    }


}
