SF3+Doctrine calculate price between dates

Question

So in my Symfony 3 project I need to create a sort of price calculator with takes 3 params: date_from, date_to, guest_number. Table in MySql looks like:

-----------------------------------------------------------------
id    |    date_from    |    date_to     |   people   |     price
-----------------------------------------------------------------
1     |    2016-01-15   |    2016-04-20  |   1        |     100
-----------------------------------------------------------------
2     |    2016-04-20   |    2016-08-15  |   1        |     200
-----------------------------------------------------------------
3     |    2016-04-20   |    2016-08-15  |   2        |     250

For example, someone choose 2016-01-01 till 2016-01-10 for 1 guest. Calculator should return 1000. And it is no big deal to create SQL statement for this

Example 2, someone choose 2016-04-15 til 2016-04-25 for 1 person.

The question is how can I build with Doctrine QueryBuilder statement which would calculate 'n' days from one period multiply price and 'n' days from another period multiply corresponding price?

Answer 1

Let's say you have an entity Calendar:

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Entity(repositoryClass="CalendarRepository")
 * @ORM\Table(name="calendar")
 */
class Calendar
{
    /**
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="date")
     */
    private $dateFrom;

    /**
     * @ORM\Column(type="date")
     */
    private $dateTo;

    /**
     * @ORM\Column(type="integer")
     */
    private $people;

    /**
     * @ORM\Column(type="integer")
     */
    private $price;
}

Then your repository class could look like this:

<?php

namespace AppBundle\Entity;

use Doctrine\ORM\EntityRepository;

class CalendarRepository extends EntityRepository
{
    public function findPriceFor(\DateTime $dateFrom, \DateTime $dateTo, $nrOfPeople)
    {
        $qb = $this->createQueryBuilder('calendar');
        $qb->select('SUM(
                        CASE 
                            WHEN calendar.dateFrom >= :dateFromChosen AND calendar.dateTo >= :dateToChosen THEN DATE_DIFF(:dateToChosen, calendar.dateFrom)
                            WHEN calendar.dateFrom <= :dateFromChosen AND calendar.dateTo >= :dateToChosen THEN DATE_DIFF(:dateToChosen, :dateFromChosen)
                            WHEN calendar.dateFrom <= :dateFromChosen AND calendar.dateTo <= :dateToChosen THEN DATE_DIFF(calendar.dateTo, :dateFromChosen)
                            WHEN calendar.dateFrom >= :dateFromChosen AND calendar.dateTo <= :dateToChosen THEN DATE_DIFF(calendar.dateTo, calendar.dateFrom)
                            ELSE 0
                        END
                    )*calendar.price AS intervalPrice');

        $qb->andWhere('calendar.people = :nrOfPeople')
            ->andWhere(
                $qb->expr()->andX(
                    $qb->expr()->lt('calendar.dateFrom', ':dateToChosen'),
                    $qb->expr()->gt('calendar.dateTo', ':dateFromChosen')
                )
            );

        $qb->setParameter('nrOfPeople', $nrOfPeople)
            ->setParameter('dateFromChosen', $dateFrom->format('Y-m-d'))
            ->setParameter('dateToChosen', $dateTo->format('Y-m-d'));

        $qb->groupBy('calendar.id');

        $query = $qb->getQuery();

        $resultArray = $query->execute();

        $totalPrice = array_sum(array_column($resultArray, 'intervalPrice'));

        return $totalPrice;
    }
}

If we took your example MySQL table, and decide to calculate a price for 1 person, from "2016-04-15" to "2016-04-25", then result would be this: