<?php

namespace App\Repository;

use App\Entity\OrdenDominio;
use Doctrine\ORM\EntityRepository;

class DominioRepository extends EntityRepository
{
    public function listarUsuario($id) {

        $entityManager = $this->getEntityManager();
        $dominios = $entityManager->createQuery(
            "SELECT
                dom.id, dom.estatus, dom.nombre, ext.nombre AS extension,
                oda.fechaInicio AS inicioA, oda.fechaFin AS finA, orda.correlativo AS correlativoA, orda.id AS ordA,
                odu.fechaInicio AS inicioU, odu.fechaFin AS finU, ordu.correlativo AS correlativoU, odu.estatus AS estatusU, ordu.id AS ordU, ordu.createdAt AS creadoU
            FROM
                App\Entity\Extension ext,
                App\Entity\Dominio dom
            INNER JOIN
                App\Entity\OrdenDominio oda
                WITH (
                    oda.id = (
                        SELECT
                            MAX(ordda.id)
                        FROM
                            App\Entity\OrdenDominio ordda
                        WHERE
                            ordda.dominio = dom.id AND
                            ordda.estatus = :ordAprobada AND
                            (
                                (
                                ordda.fechaInicio <= :hoy AND
                                ordda.fechaFin >= :hoy
                                )
                                OR
                                (
                                ordda.fechaFin <= :hoy
                                )
                            )
                    )
                )
            INNER JOIN
                App\Entity\OrdenCompra orda
                    WITH (
                        orda.id = oda.ordenCompra
                    )
            LEFT JOIN
                App\Entity\OrdenDominio odu
                    WITH (
                        odu.id = (
                            SELECT
                                MAX(orddu.id)
                            FROM
                                App\Entity\OrdenDominio orddu
                            WHERE
                                orddu.dominio = dom.id AND
                                orddu.id != oda.id
                        )
                    )
            LEFT JOIN
                App\Entity\OrdenCompra ordu
                    WITH (
                        ordu.id = odu.ordenCompra
                    )
            WHERE
                dom.extension = ext.id AND
                dom.usuario = :id
            ORDER BY
                dom.id ASC"
        )
        ->setParameter('ordAprobada', OrdenDominio::ESTATUS_APROBADA)
        ->setParameter('hoy', date('Y-m-d'))
        ->setParameter('id', $id)
        ->execute();

        return $dominios;
    }
}
