<?php

namespace RxConnexionBundle\Repository;


class FaxTranOutboundRepository extends \Doctrine\ORM\EntityRepository
{
    public function reportFaxOutBound($params) {

        $sqlWhere = "WHERE fto.`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 ") . "fto.`created_at` 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'])));
        }

        $sql = null;
        if ($params['type'] == 'an') {
            $sql = "SELECT 
                        DATE_FORMAT(fto.`created_at`, '%m/%d/%Y %h:%i %p') AS createdAt,
                        anf.`id` AS anfId,
                        fto.`fax_no` AS faxNo,
                        fto.`status`,
                        anf.`schedule_id` AS scheduleId,
                        p.`id` AS patientId, 
                        p.`full_name` AS patientName 
                    FROM `fax_tran_outbound` fto
                    INNER JOIN `appointment_notice_fax` anf ON anf.`id` = fto.`appointment_notice_fax_id`
                    LEFT JOIN `patient` p ON anf.`patient_id` = p.`id`
                    $sqlWhere
                    ORDER BY fto.`created_at` DESC
                ";
        }

        if ($params['type'] == 'pl') {
            $sql = "SELECT 
                    DATE_FORMAT(fto.`created_at`, '%m/%d/%Y %h:%i %p') AS createdAt,
                    pl.`letter_type`,
                    fto.`fax_no` AS faxNo,
                    fto.`status`,
                    pl.`patient_id` AS patientId,
                    p.`full_name` AS patientName,
                    pl.`id`
                FROM `fax_tran_outbound` fto
                INNER JOIN `patient_letter` pl ON pl.`id` = fto.`patient_letter_id`
                LEFT JOIN `patient` p ON p.`id` = pl.`patient_id`
                $sqlWhere
                ORDER BY fto.`created_at` DESC
                ";
        }

        if ($params['type'] == 'fo') {
            $sql = "SELECT 
                        DATE_FORMAT(fto.`created_at`, '%m/%d/%Y %h:%i %p') AS createdAt,
                        fto.`id`,
                        fto.`fax_no` AS faxNo,
                        fto.`status`,
                        p.`id` AS patientId, 
                        p.`full_name` AS patientName,
                        fo.`id` AS faxingOption_id
                    FROM `faxing_option_outbound` fto
                    LEFT JOIN `faxing_option` fo ON fo.`id` = fto.`faxing_option_id`
                    LEFT JOIN `patient` p ON fo.`patient_id` = p.`id`
                    WHERE fto.`is_deleted` = 0
                    ORDER BY fto.`created_at` DESC
                ";

        }

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

        $result = $query->fetchAll();

        return $result;
    }
}
