<?php

namespace RxConnexionBundle\Repository;

/**
 * PatientChecklistRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class PatientChecklistRepository extends \Doctrine\ORM\EntityRepository
{
    public function reportPatientChecklist($params) {
        $sqlWhere = "WHERE p.`is_deleted` = 0";
        $stmtParams = array();

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

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

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

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

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

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

        $sql = "
            SELECT 
                p.`full_name` AS patientFullName,
                DATE_FORMAT(p.`referral_date`, '%m/%d/%Y') AS referralDate,
                DATE_FORMAT(p.`receive_date`, '%m/%d/%Y') AS receiveDate,
                rp.`full_name` AS referringProvider,
                rhf.`description` AS referringHealthFacility,
                (
                    SELECT
                        GROUP_CONCAT(CONCAT_WS('|', c.`id`, c.`description`) SEPARATOR ',') AS patientChecklist_checked
                    FROM `checklist` c
                    WHERE c.`is_deleted` = 0
                    AND c.`id` IN(
                        SELECT
                            pc.`checklist_id`
                        FROM `patient_checklist` pc
                        WHERE pc.`patient_id` = p.`id`
                        AND pc.`is_deleted` = 0
                    )
                ) AS patientChecklist_Checked,
                (
                    SELECT
                        GROUP_CONCAT(CONCAT_WS('|', c.`id`, c.`description`) SEPARATOR ',') AS patientChecklist_Unchecked
                    FROM `checklist` c
                    WHERE c.`is_deleted` = 0
                    AND c.`id` NOT IN(
                        SELECT
                            pc.`checklist_id`
                        FROM `patient_checklist` pc
                        WHERE pc.`patient_id` = p.`id`
                        AND pc.`is_deleted` = 0
                    )
                ) AS patientChecklist_Unchecked
            FROM `patient` p
            LEFT JOIN `patient_health_facility` phf ON phf.`id` = (
                SELECT
                    phf1.`id`
                FROM `patient_health_facility` phf1
                WHERE (phf1.`end_date` IS NULL OR phf1.`end_date` != '')
                AND phf1.`patient_id` = p.`id`
                ORDER BY phf1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `health_facility` hf ON phf.`health_facility_id` = hf.`id`
            LEFT JOIN `company` co ON co.`id` = hf.`company_id`
            LEFT JOIN `patient_referring_provider` prp ON prp.`id` = (
                SELECT
                    prp1.`id`
                FROM `patient_referring_provider` prp1
                WHERE (prp1.`end_date` IS NULL OR prp1.`end_date` != '')
                AND prp1.`patient_id` = p.`id`
                ORDER BY prp1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `referring_provider` rp ON prp.`referring_provider_id` = rp.`id`
            LEFT JOIN `patient_referring_health_facility` prhf ON prhf.`id` = (
                SELECT
                    prhf1.`id`
                FROM `patient_referring_health_facility` prhf1
                WHERE (prhf1.`end_date` IS NULL OR prhf1.`end_date` != '')
                AND prhf1.`patient_id` = p.`id`
                ORDER BY prhf1.`created_at` DESC
                LIMIT 1
            )
            LEFT JOIN `referring_health_facility` rhf ON prhf.`referring_health_facility_id` = rhf.`id`
            $sqlWhere
        ";

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

        $query->execute($stmtParams);

        $result = $query->fetchAll();

        return $result;
    }
}
