<?php

namespace AppBundle\Entity\Repository;

use AppBundle\Entity\Connection;
use AppBundle\Entity\Port;
use Doctrine\ORM\EntityRepository;

class ConnectionRepository extends EntityRepository
{
    /**
     * @return \Doctrine\ORM\Query
     */
    public function getListQuery()
    {
        return $this
            ->createQueryBuilder('c')
            ->where('c.isRemoved != true')
            ->orWhere('c.isRemoved IS NULL')
            ->getQuery();
    }

    /**
     * @return \Doctrine\ORM\Query
     */
    public function getRemovedListQuery()
    {
        return $this
            ->createQueryBuilder('c')
            ->where('c.isRemoved = true')
            ->getQuery();
    }

    /**
     * @return Connection[]
     */
    public function findNotRemoved()
    {
        return $this
            ->createQueryBuilder('c')
            ->select('c, loading', 'unloading')
            ->leftJoin('c.loading', 'loading')
            ->leftJoin('c.unloading', 'unloading')
            ->where('c.isRemoved != true')
            ->orWhere('c.isRemoved IS NULL')
            ->getQuery()
            ->getResult();
    }

    /**
     * @param $loading
     * @param $unloading
     * @return \AppBundle\Entity\Connection||null
     */
    public function findOneByPortNames($loading, $unloading)
    {
        return $this
            ->createQueryBuilder('c')
            ->select('c, loading', 'unloading')
            ->leftJoin('c.loading', 'loading')
            ->leftJoin('c.unloading', 'unloading')
            ->where('c.isRemoved != true OR c.isRemoved IS NULL')
            ->andWhere('loading.name = :loading')
            ->andWhere('unloading.name = :unloading')
            ->setParameter('loading', $loading)
            ->setParameter('unloading', $unloading)
            ->setMaxResults(1)
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @param $loading
     * @param $unloading
     * @return Connection||null
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function findOneByPortsName($loading, $unloading)
    {
        return $this
            ->createQueryBuilder('c')
            ->select('c, loading', 'unloading')
            ->leftJoin('c.loading', 'loading')
            ->leftJoin('c.unloading', 'unloading')
            ->where('loading.name = :loading')
            ->andWhere('unloading.name = :unloading')
            ->where('c.isRemoved != true')
            ->orWhere('c.isRemoved IS NULL')
            ->setParameter('loading', $loading)
            ->setParameter('unloading', $unloading)
            ->setMaxResults(1)
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @param $loading
     * @param $unloading
     * @return Connection||null
     * @throws \Doctrine\ORM\NonUniqueResultException
     */
    public function findOneByPorts(Port $loading, Port $unloading)
    {
        return $this
            ->createQueryBuilder('c')
            ->where('c.loading = :loading')
            ->andWhere('c.unloading = :unloading')
            ->andWhere('c.isRemoved != true OR c.isRemoved IS NULL')
            ->andWhere('c.validTo >= :validTo')
            ->setParameter('loading', $loading)
            ->setParameter('unloading', $unloading)
            ->setParameter('validTo', new \DateTime())
            ->setMaxResults(1)
            ->getQuery()
            ->getOneOrNullResult();
    }

    /**
     * @param Port $port
     * @return array
     */
    public function findAllData()
    {
//        return $this
//            ->createQueryBuilder('connection')
//            ->select('loading.id as l', 'unloading.id as u', 'connection.cost as c')
//            ->leftJoin('connection.loading', 'loading')
//            ->leftJoin('connection.unloading', 'unloading')
//            ->where('connection.isRemoved != true')
//            ->orWhere('connection.isRemoved IS NULL')
//            ->getQuery()
//            ->getResult();

        $em = $this->getEntityManager();

        return $em
            ->createQuery("
                SELECT loading.id as l, unloading.id as u, connection.cost as c, connection.validTo as vt, connection.updatedAt as ua, connection.createdAt as ca, connection.useeuro FROM AppBundle:Connection connection
                LEFT JOIN connection.loading loading
                LEFT JOIN connection.unloading unloading
                WHERE
                    connection.validTo >= :validTo AND
                    (connection.isRemoved != true OR connection.isRemoved IS NULL)
            ")
            ->setParameter('validTo', new \DateTime())
            ->getResult();
    }
}
