SF3 + Doctrine计算日期之间的价格

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?

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: