<?php

namespace App\Repository;

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

class HostingRepository extends EntityRepository
{
    public function listarUsuario($usuarioId) {

        $sql = "SELECT
                hst.id, hst.estatus, pln.nombre AS plan,
                oha.detalle AS detalleA, oha.fechaInicio AS inicioA, oha.fechaFin AS finA, orda.correlativo AS correlativoA, orda.id AS ordA,
                ohu.detalle AS detalleU, ohu.fechaInicio AS inicioU, ohu.fechaFin AS finU, ordu.correlativo AS correlativoU, ohu.estatus AS estatusU, ordu.id AS ordU, ordu.createdAt AS creadoU
            FROM
                App\Entity\Plan pln,
                App\Entity\Hosting hst
            INNER JOIN
                App\Entity\OrdenHosting oha
                WITH (
                    oha.id = (
                        SELECT
                            MAX(ordha.id)
                        FROM
                            App\Entity\OrdenHosting ordha
                        WHERE
                            ordha.hosting = hst.id AND
                            ordha.estatus = :orhAprobada AND
                            (
                                (
                                ordha.fechaInicio <= :hoy AND
                                ordha.fechaFin >= :hoy
                                )
                                OR
                                (
                                ordha.fechaFin <= :hoy
                                )
                            )
                    )
                )
            INNER JOIN
                App\Entity\OrdenCompra orda
                    WITH (
                        orda.id = oha.ordenCompra
                    )
            LEFT JOIN
                App\Entity\OrdenHosting ohu
                    WITH (
                        ohu.id = (
                            SELECT
                                MAX(ordhu.id)
                            FROM
                                App\Entity\OrdenHosting ordhu
                            WHERE
                                ordhu.hosting = hst.id AND 
                                ordhu.id != oha.id
                        )
                    )
            LEFT JOIN
                App\Entity\OrdenCompra ordu
                    WITH (
                        ordu.id = ohu.ordenCompra
                    )
            WHERE
                hst.plan = pln.id AND
                hst.usuario = :id
            ORDER BY
                hst.id ASC";

        $entityManager = $this->getEntityManager();
        $hostings = $entityManager->createQuery($sql)
        ->setParameter('orhAprobada', OrdenHosting::ESTATUS_APROBADA)
        ->setParameter('hoy', date('Y-m-d'))
        ->setParameter('id', $usuarioId)
        ->execute();

        return $hostings;
    }


    public function hostingDataSimple($hostingId) {

        $sql = "SELECT
                hst.id, hst.estatus, pln.nombre AS plan,
                usu.username, usu.email, usu.estatus AS estatusUsuario, usu.id AS usuarioId, prs.nombre AS persona,
                oha.detalle AS detalleA, oha.fechaInicio AS inicioA, oha.fechaFin AS finA, orda.correlativo AS correlativoA, orda.id AS ordA,
                ohu.detalle AS detalleU, ohu.fechaInicio AS inicioU, ohu.fechaFin AS finU, ordu.correlativo AS correlativoU, ohu.estatus AS estatusU, ordu.id AS ordU, ordu.createdAt AS creadoU
            FROM
                App\Entity\Plan pln,
                App\Entity\Hosting hst
            INNER JOIN
                App\Entity\Usuario usu
                WITH (
                    hst.usuario = usu.id
                )
            INNER JOIN
                App\Entity\Persona prs
                WITH (
                    usu.persona = prs.id
                )
            INNER JOIN
                App\Entity\OrdenHosting oha
                WITH (
                    oha.id = (
                        SELECT
                            MAX(ordha.id)
                        FROM
                            App\Entity\OrdenHosting ordha
                        WHERE
                            ordha.hosting = hst.id AND
                            ordha.estatus = :orhAprobada AND
                            (
                                (
                                ordha.fechaInicio <= :hoy AND
                                ordha.fechaFin >= :hoy
                                )
                                OR
                                (
                                ordha.fechaFin <= :hoy
                                )
                            )
                    )
                )
            INNER JOIN
                App\Entity\OrdenCompra orda
                    WITH (
                        orda.id = oha.ordenCompra
                    )
            LEFT JOIN
                App\Entity\OrdenHosting ohu
                    WITH (
                        ohu.id = (
                            SELECT
                                MAX(ordhu.id)
                            FROM
                                App\Entity\OrdenHosting ordhu
                            WHERE
                                ordhu.hosting = hst.id AND 
                                ordhu.id != oha.id
                        )
                    )
            LEFT JOIN
                App\Entity\OrdenCompra ordu
                    WITH (
                        ordu.id = ohu.ordenCompra
                    )
            WHERE
                hst.plan = pln.id AND
                hst.id = :id
            ORDER BY
                hst.id ASC";

        $entityManager = $this->getEntityManager();
        $hosting = $entityManager->createQuery($sql)
        ->setParameter('orhAprobada', OrdenHosting::ESTATUS_APROBADA)
        ->setParameter('hoy', date('Y-m-d'))
        ->setParameter('id', $hostingId)
        ->execute();

        return $hosting;
    }
}
