Symfony Profiler

<?php
namespace AdminBundle\Repository;
use AdminBundle\Entity\Booking;
use AdminBundle\Entity\BroadcastedDriver;
use AdminBundle\Entity\Driver;
use AdminBundle\Entity\Settings;
use AdminBundle\Entity\User;
use DateInterval;
use DateTime;
use Doctrine\ORM\QueryBuilder;
/**
* BookingRepository
*
* This class was generated by the Doctrine ORM. Add your own custom
* repository methods below.
*/
class BookingRepository extends \Doctrine\ORM\EntityRepository
{
const LIST_INCOMING = 'incoming';
const LIST_LIVE = 'live';
const LIST_COMPLETED = 'completed';
const LIST_CANCELLED = 'cancelled';
const LIST_UNASSIGNED = 'unassigned';
const LIST_STATUSES = [
self::LIST_INCOMING,
self::LIST_LIVE,
self::LIST_COMPLETED,
self::LIST_CANCELLED,
self::LIST_UNASSIGNED,
];
public function getDriverCurrentBooking($driverId)
{
$em = $this->getEntityManager();
$conn = $em->getConnection();
$status = [
Booking::STATUS_ON_THE_WAY,
Booking::STATUS_ARRIVED_AND_WAITING,
Booking::STATUS_PASSENGER_ON_BOARD,
Booking::STATUS_ABOUT_TO_DROP,
];
$sql = 'SELECT id, booking_status FROM booking WHERE driver_id = ' . $driverId . ' AND booking_status IN(' . implode($status, ',') . ')';
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return !empty($result[0]) ? $result[0] : null;
}
public function getUpdatedBookingsCount($driverId)
{
$em = $this->getEntityManager();
$conn = $em->getConnection();
$sql = 'SELECT count(id) as notifications FROM notification_booking_update WHERE driver_id=' . $driverId . ' AND seen=false';
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();
return intval($result[0]['notifications']);
}
public function getNewBookingsCount($driverId)
{
$queryBuilder = $this->createQueryBuilder('b')
->select('count(b.id)');
$queryBuilder
->leftJoin('b.broadcastedDrivers', 'broadcasted')
->where($queryBuilder->expr()->orX(
$queryBuilder->expr()->andX(
$queryBuilder->expr()->eq('b.status', Booking::STATUS_BROADCAST),
$queryBuilder->expr()->eq('broadcasted.driver', $driverId),
$queryBuilder->expr()->eq('broadcasted.status', BroadcastedDriver::STATUS_WAITING)
),
$queryBuilder->expr()->andX(
$queryBuilder->expr()->eq('b.status', Booking::STATUS_DISPATCHED),
$queryBuilder->expr()->eq('b.driver', $driverId)
)
));
return intval($queryBuilder->getQuery()->getSingleScalarResult());
}
public function getUnassignedBookingsAvailableCount(Driver $driver)
{
$bookings = $this->getUnassignedBookingList($driver);
return count($bookings);
}
/**
* @param $type
* @param Driver $driver
* @return array
*/
public function getBookingList($type, Driver $driver)
{
$queryBuilder = $this->createQueryBuilder('b');
switch ($type) {
case self::LIST_INCOMING:
$queryBuilder
->leftJoin('b.broadcastedDrivers', 'broadcasted')
->where($queryBuilder->expr()->orX(
$queryBuilder->expr()->andX(
$queryBuilder->expr()->eq('b.status', Booking::STATUS_BROADCAST),
$queryBuilder->expr()->eq('broadcasted.driver', $driver->getId()),
$queryBuilder->expr()->eq('broadcasted.status', BroadcastedDriver::STATUS_WAITING)
),
$queryBuilder->expr()->andX(
$queryBuilder->expr()->eq('b.status', Booking::STATUS_DISPATCHED),
$queryBuilder->expr()->eq('b.driver', $driver->getId())
)
))
;
break;
case self::LIST_LIVE:
$queryBuilder->where('b.status in (:status)')
->andWhere('b.driver = :driver')
->setParameter('status', [
Booking::STATUS_DRIVER_ACCEPT,
Booking::STATUS_ON_THE_WAY,
Booking::STATUS_ARRIVED_AND_WAITING,
Booking::STATUS_PASSENGER_ON_BOARD,
Booking::STATUS_ABOUT_TO_DROP
])
->setParameter('driver', $driver);
break;
case self::LIST_COMPLETED:
$queryBuilder->where('b.status = :status')
->andWhere('b.driver = :driver')
->setParameter('status', Booking::STATUS_COMPLETED)
->setParameter('driver', $driver->getId());
break;
case self::LIST_CANCELLED:
$queryBuilder->where('b.status in (:status)')
->andWhere('b.driver = :driver')
->setParameter('status', [
Booking::STATUS_CANCELLED_OTHER_REASON,
Booking::STATUS_CLIENT_CANCELLATION,
Booking::STATUS_CLIENT_NOT_SHOW_UP,
Booking::STATUS_CAR_BROKE_DOWN,
Booking::STATUS_OFFICE_CANCELLATION
])
->setParameter('driver', $driver);
break;
}
$queryBuilder->andWhere('DATE(b.pickUpDate) >= DATE(:lastWeekMonday)')
->setParameter('lastWeekMonday', new \DateTime('last week last mon'));
return $queryBuilder->getQuery()->getResult();
}
/**
* @param $type
* @param User $user
* @return array
*/
public function getClientBookingList($type, User $user)
{
$queryBuilder = $this->createQueryBuilder('b');
switch ($type) {
case self::LIST_LIVE:
$queryBuilder
->where('b.status in (:status)')
->andWhere('b.clientUser = :client')
->setParameter('status', [
Booking::STATUS_NEW_BOOKING,
Booking::STATUS_DISPATCHED,
Booking::STATUS_DRIVER_ACCEPT,
Booking::STATUS_ON_THE_WAY,
Booking::STATUS_ARRIVED_AND_WAITING,
Booking::STATUS_PASSENGER_ON_BOARD,
Booking::STATUS_ABOUT_TO_DROP,
])
->setParameter('client', $user);
break;
case self::LIST_COMPLETED:
$queryBuilder
->where('b.status = :status')
->andWhere('b.clientUser = :client')
->setParameter('status', Booking::STATUS_COMPLETED)
->setParameter('client', $user)
->orderBy('b.pickUpDateTime', 'DESC')
;
break;
case self::LIST_CANCELLED:
$queryBuilder
->where('b.status in (:status)')
->andWhere('b.client = :client')
->setParameter('status', [
Booking::STATUS_CANCELLED_OTHER_REASON,
Booking::STATUS_CLIENT_CANCELLATION,
Booking::STATUS_CLIENT_NOT_SHOW_UP,
Booking::STATUS_CAR_BROKE_DOWN,
Booking::STATUS_OFFICE_CANCELLATION
])
->setParameter('client', $user);
break;
case 'all':
$queryBuilder
->where('b.status in (:status)')
->andWhere('b.clientUser = :client')
->setParameter('status', [
Booking::STATUS_NEW_BOOKING,
Booking::STATUS_DISPATCHED,
Booking::STATUS_CANCELLED_OTHER_REASON,
Booking::STATUS_CLIENT_CANCELLATION,
Booking::STATUS_CLIENT_NOT_SHOW_UP,
Booking::STATUS_CAR_BROKE_DOWN,
Booking::STATUS_OFFICE_CANCELLATION,
Booking::STATUS_COMPLETED,
Booking::STATUS_DRIVER_ACCEPT,
Booking::STATUS_ON_THE_WAY,
Booking::STATUS_ARRIVED_AND_WAITING,
Booking::STATUS_PASSENGER_ON_BOARD,
Booking::STATUS_ABOUT_TO_DROP,
])
->setParameter('client', $user)
->orderBy('b.pickUpDateTime', 'DESC')
;
break;
}
return $queryBuilder->getQuery()->getResult();
}
public function findByPickUpDate($date, $driverName = null)
{
$queryBuilder = $this->createQueryBuilder('b')
->innerJoin('b.driver', 'd')
->leftJoin('b.returnBooking', 'r')
->innerJoin('d.user', 'u')
->where('b.pickUpDate = :date')
->setParameter('date', $date->format('Y-m-d'));
if ($driverName) {
$queryBuilder->andWhere($queryBuilder->expr()->orX(
$queryBuilder->expr()->like('u.firstname', $queryBuilder->expr()->literal('%' . $driverName . '%')),
$queryBuilder->expr()->like('u.lastname', $queryBuilder->expr()->literal('%' . $driverName . '%')),
$queryBuilder->expr()->like($queryBuilder->expr()->concat(
'u.firstname', $queryBuilder->expr()->concat(
$queryBuilder->expr()->literal(' '), 'u.lastname'
)
),
$queryBuilder->expr()->literal($driverName . '%')
)
));
}
return $queryBuilder->getQuery()->getResult();
}
public function createUnallocatedBookingsQb()
{
return $this
->createQueryBuilder('b')
->where('b.driver IS NULL')
->andWhere('b.status = :status')
->setParameter('status', Booking::STATUS_NEW_BOOKING)
;
}
public function findUnallocatedBookings($date, $isArray = true)
{
$queryBuilder = $this->createUnallocatedBookingsQb();
if ($isArray) {
$queryBuilder->select('b.id, b.key, b.pickUpPostCode, b.destinationPostCode, b.pickUpTime, b.matchJob');
}
return $queryBuilder
->andWhere('b.pickUpDate = :date')
->setParameter('date', $date->format('Y-m-d'))
->orderBy('b.pickUpTime')
->getQuery()
->getResult()
;
}
public function findUnallocatedBookingsCreatedBetweenDates(DateTime $startDate, DateTime $endDate)
{
$qb = $this
->createQueryBuilder('b')
->where('b.driver IS NULL')
->andWhere('b.status = :status')
;
// TODO: These will be removed after we cover future bookings with this feature
$startDatePickUp = clone $startDate;
$startDatePickUp->add(new \DateInterval('P3D'));
$endDatePickUp = clone $endDate;
$endDatePickUp->add(new \DateInterval('P3D'));
return $qb
->andWhere($qb->expr()->orX(
$qb->expr()->between('b.bookingDate', ':startDate', ':endDate'),
$qb->expr()->between('b.pickUpDateTime', ':startDatePickUp', ':endDatePickUp')
))
->setParameter('status', Booking::STATUS_NEW_BOOKING)
->setParameter('startDate', $startDate)
->setParameter('endDate', $endDate)
->setParameter('startDatePickUp', $startDatePickUp)
->setParameter('endDatePickUp', $endDatePickUp)
->getQuery()
->getResult()
;
}
public function findDispatchedAcceptBetweenDates(DateTime $startDate, DateTime $endDate)
{
$qb = $this
->createQueryBuilder('b')
->where('b.driver IS NOT NULL')
->andWhere('b.status = :status')
;
return $qb
->andWhere(
$qb->expr()->between('b.pickUpDateTime', ':startDate', ':endDate')
)
->setParameter('status', Booking::STATUS_DRIVER_ACCEPT)
->setParameter('startDate', $startDate)
->setParameter('endDate', $endDate)
->getQuery()
->getResult()
;
}
public function findUnallocatedBookingsForCarTypeBeforeTime($date, $time, $carType, $isArray = true)
{
$queryBuilder = $this->createQueryBuilder('b');
if ($isArray) {
$queryBuilder->select('b.id, b.key, b.pickUpPostCode, b.destinationPostCode, b.pickUpTime, b.matchJob');
}
$queryBuilder
->where('b.pickUpDate = :date')
->andWhere('b.pickUpTime < :time')
->andWhere('b.status = :status');
$queryBuilder
->join('b.carType', 'ct')
->andWhere('ct.id = :carType OR :carType MEMBER OF ct.replacements')
->setParameter('carType', $carType);
$queryBuilder
->orderBy('b.pickUpTime')
->setParameter('time', $time)
->setParameter('status', Booking::STATUS_NEW_BOOKING)
->setParameter('date', $date->format('Y-m-d'));
return $queryBuilder->getQuery()->getResult();
}
public function findMatchedJobs($date)
{
$queryBuilder = $this->createQueryBuilder('b')
//->select('b.id, b.key, b.pickUpPostCode, b.destinationPostCode, b.pickUpTime, b.matchJob')
->where('b.matchJob IS NOT NULL')
->andWhere('b.driver IS NULL')
->andWhere('b.status = :status')
->andWhere('b.pickUpDate = :date')
->orderBy('b.pickUpTime')
->setParameter('status', Booking::STATUS_NEW_BOOKING)
->setParameter('date', $date->format('Y-m-d'));
return $queryBuilder->getQuery()->getResult();
}
public function deletePreallocationBookings($date)
{
$queryBuilder = $this->createQueryBuilder('b')
->update()
->set('b.driver', 'null')
->where('b.driver IS NOT NULL')
->andWhere('b.pickUpDate = :date')
->andWhere('b.status = :status')
->setParameter('status', Booking::STATUS_NEW_BOOKING)
->setParameter('date', $date->format('Y-m-d'));
$queryBuilder->getQuery()->execute();
}
public function deletePreallocationWithoutMatchedBookings($date)
{
$queryBuilder = $this->createQueryBuilder('b')
->update()
->set('b.driver', 'null')
->where('b.driver IS NOT NULL')
->andWhere('b.matchJob IS NOT NULL')
->andWhere('b.pickUpDate = :date')
->andWhere('b.status = :status')
->setParameter('status', Booking::STATUS_NEW_BOOKING)
->setParameter('date', $date->format('Y-m-d'));
$queryBuilder->getQuery()->execute();
}
/**
* @param Driver $driver
* @param null $day
* @return array
*/
public function getUnassignedBookingList(Driver $driver, $day = null)
{
$undispatched_time_start = $this->getEntityManager()->getRepository(Settings::class)->findOneBy(['key' => 'undispatched_time_start'])->getValue();
$undispatched_time_limit = $this->getEntityManager()->getRepository(Settings::class)->findOneBy(['key' => 'undispatched_time_limit'])->getValue();
$undispatched_interval_between_bookings_pickup = $this->getEntityManager()->getRepository(Settings::class)->findOneBy(['key' => 'undispatched_interval_between_bookings_pickup']);
$undispatched_interval_between_bookings_dropoff = $this->getEntityManager()->getRepository(Settings::class)->findOneBy(['key' => 'undispatched_interval_between_bookings_dropoff']);
$st = new \DateTime('now');
$et = new \DateTime('now');
$yt = new \DateTime('now');
//daca exista parametrul day, prima zi incepe de la +8 ore (deoarece de la +4 pana la +8 ore se aplica primul interval, ziua curenta)
if (!empty($day)) {
$yt = (new \DateTime('now'))->add(new \DateInterval('P' . $day . 'D'));
$st = ($st->add(new \DateInterval('P' . $day . 'D')));
$et = ($et->add(new \DateInterval('P' . $day . 'D')));
$et->setTime(23, 59);
$yt = ($yt->sub(new \DateInterval('P1D')));
if ($day == 1) {
$startTime = ($st->add(new \DateInterval('PT' . $undispatched_time_limit . 'H')))->format('Y-m-d H:i:s');
} else {
$st->setTime(00, 00);
$startTime = $st->format('Y-m-d H:i:s');
}
$endTime = $et->format('Y-m-d H:i:s');
} else {
$startTime = ($st->add(new \DateInterval('PT' . $undispatched_time_start . 'H')))->format('Y-m-d H:i:s');
$endTime = ($et->add(new \DateInterval('PT' . $undispatched_time_limit . 'H')))->format('Y-m-d H:i:s');
}
$result = [];
/** @var QueryBuilder $queryBuilder */
$queryBuilder = $this->createQueryBuilder('b');
$queryBuilder
->where('b.driver is NULL');
$queryBuilder
->andWhere('b.status = :statusNew or b.status = :statusBroadcast')
->setParameter('statusNew', Booking::STATUS_NEW_BOOKING)->setParameter('statusBroadcast', Booking::STATUS_BROADCAST);
$queryBuilder
->join('b.carType', 'ct')
->andWhere('ct.id = :carType OR :carType MEMBER OF ct.replacements')
->setParameter('carType', $driver->getCar()->getType()->getId());
$queryBuilder
->andWhere('b.pickUpDateTime >= :startTime and b.pickUpDateTime <= :endTime')->setParameter('startTime', $startTime)->setParameter('endTime', $endTime);
$queryBuilder
->orderBy('b.pickUpDateTime', 'ASC');
$result = array_merge($result, $queryBuilder->getQuery()->getResult());
$bookings = array_unique($result, SORT_REGULAR);
/** @var QueryBuilder $driverQueryBuilder */
$driverQueryBuilder = $this->createQueryBuilder('b');
$driverQueryBuilder
->where('b.driver = :driver')
->setParameter('driver', $driver);
$driverQueryBuilder
->andWhere('b.pickUpDate = :yesterday or b.pickUpDate = :startDate or b.pickUpDate = :endDate')
->setParameter('startDate', (new \DateTime($startTime))->format('Y-m-d'))
->setParameter('endDate', (new \DateTime($endTime))->format('Y-m-d'))
->setParameter('yesterday', $yt->format('Y-m-d'));
$driverBookings = array_unique($driverQueryBuilder->getQuery()->getResult(), SORT_REGULAR);
if (!empty($driverBookings) and !empty($bookings)) {
/** @var Booking $dBook */
foreach ($driverBookings as $dBook) {
$estimated = explode(':', $dBook->getEstimatedTime());
$bookingPickup = (new \DateTime($dBook->getPickUpDateTime()->format('Y-m-d H:i')))->format('Y-m-d H:i');
$ed = new \DateTime($dBook->getPickUpDateTime()->format('Y-m-d H:i'));
$bookingEnd = ($ed->add(new \DateInterval('PT' . $estimated[0] . 'H' . $estimated[1] . 'M')));
/** @var Booking $fBook */
foreach ($bookings as $key => $fBook) {
if ($bookingPickup >= $startTime and $bookingPickup <= $endTime and date_diff($bookingEnd, $fBook->getPickUpDateTime()) <= $undispatched_interval_between_bookings_pickup) {
unset($bookings[$key]);
}
}
}
}
if ($day == null) {
if (!empty($bookings)) {
$settingsRepo = $this->getEntityManager()->getRepository(Settings::class);
/** @var Booking $fBook */
foreach ($bookings as $key => $fBook) {
$driver_rating_max_limit = floatval($settingsRepo->findOneByKey('driver_rating_max_limit')->getValue());
$driver_current_rating = floatval($driver->getOverallRating());
if ($driver_current_rating < $driver_rating_max_limit) {
$vehicle_undispatched_max_price = (!empty($fBook->getCarType()) && !empty($fBook->getCarType()->getMaxPriceUndispatchedBooking())) ? floatval($fBook->getCarType()->getMaxPriceUndispatchedBooking()) : 0;
if ($vehicle_undispatched_max_price > 0) {
if (!empty($fBook->getDriverPrice())) {
if ($fBook->getDriverPrice() > $vehicle_undispatched_max_price) {
unset($bookings[$key]);
}
} elseif (!empty($fBook->getOverridePrice())) {
if ($fBook->getOverridePrice() > $vehicle_undispatched_max_price) {
unset($bookings[$key]);
}
} elseif (!empty($fBook->getQuotePrice())) {
if ($fBook->getQuotePrice() > $vehicle_undispatched_max_price) {
unset($bookings[$key]);
}
}
}
}
}
}
}
return $bookings;
}
/**
* @param Driver $driver
* @param null $day
* @param bool $timeLimit
* @return array
*/
public function getUnassignedBookingListV2(Driver $driver, $day = null,$undispatchedTimeLimit = true)
{
$settingsRepo = $this->getEntityManager()->getRepository(Settings::class);
$undispatched_time_start = $settingsRepo->findOneByKey('undispatched_time_start')->getValue();
$undispatched_time_limit = $settingsRepo->findOneByKey('undispatched_time_limit')->getValue();
$unassigned_bookings_start_period = $settingsRepo->findOneByKey('unassigned_bookings_start_period')->getValue();
$undispatched_interval_between_bookings_pickup = $settingsRepo->findOneByKey('undispatched_interval_between_bookings_pickup')->getValue();
$undispatched_interval_between_bookings_dropoff = $settingsRepo->findOneByKey('undispatched_interval_between_bookings_dropoff')->getValue();
$driver_bonus_feature_active = $settingsRepo->findOneBy([
'key' => 'driver_bonus_feature_active',
'type' => Settings::TYPE_GLOBAL,
])->getValue();
$driver_bonus_week_active = $settingsRepo->findOneBy([
'key' => 'driver_bonus_week_active',
'type' => Settings::TYPE_GLOBAL,
])->getValue();
if (!empty($driver_bonus_week_active)) {
$driver_bonus_week_active = explode(',', str_replace(' ', '', $driver_bonus_week_active));
}
// DRIVER RATING LIMITS
$driver_rating_max_limit = floatval($settingsRepo->findOneByKey('driver_rating_max_limit')->getValue());
$driver_rating_min_limit = floatval($settingsRepo->findOneByKey('driver_rating_min_limit')->getValue());
$driver_rating_lowest_limit = floatval($settingsRepo->findOneByKey('driver_rating_lowest_limit')->getValue());
$driver_current_rating = floatval($driver->getOverallRating());
$currentWeek = date('W', strtotime('monday this week'));
$st = new \DateTime('now');
$et = new \DateTime('now');
$preSt = new \DateTime('now');
//daca exista parametrul day, prima zi incepe de la +8 ore (deoarece de la +4 pana la +8 ore se aplica primul interval, ziua curenta)
if (!empty($day) && $driver_current_rating > $driver_rating_lowest_limit) {
if ($day <= 7) {
$day8=$day+1; // added 1 day to 7 days interval to cover the margins of 7 days interval for calculations of bookings
$preSt = ($preSt->add(new \DateInterval('PT' . $unassigned_bookings_start_period . 'H')));
$st = ($st->add(new \DateInterval('PT' . $unassigned_bookings_start_period . 'H')));
$et = ($et->add(new \DateInterval('P' . $day8 . 'D')));
$et->setTime(23, 59);
} elseif ($day > 7 && $driver_current_rating > $driver_rating_min_limit) {
// TODO: @cristi de rezolvat problema cu day=day+15 in cazul in care day>7 apar bookings si dupa perioada maxima definita pt period 2
// if ($driver_current_rating >= $driver_rating_max_limit) {
// $day = $day + 15;
// }
$preSt = ($preSt->add(new \DateInterval('P8D')));
$st = ($st->add(new \DateInterval('P8D')));
$et = ($et->add(new \DateInterval('P' . $day . 'D')));
$et->setTime(23, 59);
} else {
$st = $st->add(new \DateInterval('PT' . $undispatched_time_start . 'H'));
$et = $et->add(new \DateInterval('PT' . $undispatched_time_limit . 'H'));
}
$preStartTime = ($preSt->sub(new \DateInterval('P1D')))->format('Y-m-d H:i:s');
$startTime = $st->format('Y-m-d H:i:s');
$endTime = $et->format('Y-m-d H:i:s');
} else {
$preStartTime = ($preSt->sub(new \DateInterval('P1D')))->format('Y-m-d H:i:s');
if ($undispatchedTimeLimit) {
$startTime = ($st->add(new \DateInterval('PT' . $undispatched_time_start . 'H')))->format('Y-m-d H:i:s');
$endTime = ($et->add(new \DateInterval('PT' . $undispatched_time_limit . 'H')))->format('Y-m-d H:i:s');
} else {
$startTime = $st->format('Y-m-d H:i:s');
$endTime = ($et->add(new \DateInterval('P1D')))->format('Y-m-d H:i:s');
}
}
/** @var QueryBuilder $driverQueryBuilder */
$driverQueryBuilder = $this->createQueryBuilder('b');
$driverQueryBuilder
->where('b.driver = :driver')
->setParameter('driver', $driver);
$driverQueryBuilder
->andWhere('b.pickUpDate >= :preStartTime and b.pickUpDate < :endDate')
->setParameter('endDate', (new \DateTime($endTime))->format('Y-m-d'))
->setParameter('preStartTime', (new \DateTime($preStartTime))->format('Y-m-d'));
$driverBookings = array_unique($driverQueryBuilder->getQuery()->getResult(), SORT_REGULAR);
$excludedBookingTimes = [];
$excludeStartDates = [];
if (!empty($driverBookings)) {
// /** @var Booking $dBook */
// foreach ($driverBookings as $dBook) {
// $estimated = explode(':', $dBook->getEstimatedTime());
// $bookingPickup = (new \DateTime($dBook->getPickUpDateTime()->format('Y-m-d H:i')))->format('Y-m-d H:i');
// $ed = new \DateTime($dBook->getPickUpDateTime()->format('Y-m-d H:i'));
// $bookingEnd = ($ed->add(new \DateInterval('PT' . $estimated[0] . 'H' . $estimated[1] . 'M')));
// $bookingEnd = ($ed->add(new \DateInterval('PT' . $undispatched_interval_between_bookings_dropoff . 'H')));
// $excludeStartDates[] = $bookingPickup;
// $excludedBookingTimes[] = [
// 'startDate' => $bookingPickup,
// 'endDate' => $bookingEnd->format('Y-m-d H:i'),
// ];
// }
/** @var Booking $dBook */
foreach ($driverBookings as $dBook) {
$estimated = explode(':', $dBook->getEstimatedTime());
$starDate = clone $dBook->getPickUpDateTime();
$endDate = clone $dBook->getPickUpDateTime();
$endDate = $endDate->add(
new \DateInterval('PT'. $estimated[0]. 'H'. $estimated[1]. 'M')
);
$excludeStartDates[] = $starDate->format('Y-m-d H:i');
$excludedBookingTimes[] = [
'startDate' => $starDate,
'endDate' => $endDate,
];
}
}
$result = [];
/** @var QueryBuilder $queryBuilder */
$queryBuilder = $this->createQueryBuilder('b');
$queryBuilder
->where('b.driver is NULL');
$queryBuilder
->andWhere('b.status = :statusNew')
->setParameter('statusNew', Booking::STATUS_NEW_BOOKING);
$queryBuilder
->join('b.carType', 'ct')
->andWhere('ct.id = :carType OR :carType MEMBER OF ct.replacements')
->setParameter('carType', $driver->getCar()->getType()->getId());
$queryBuilder
->andWhere('b.pickUpDateTime >= :startTime and b.pickUpDateTime <= :endTime')->setParameter('startTime', $startTime)->setParameter('endTime', $endTime);
if (!empty($excludeStartDates)) {
$queryBuilder
->andWhere('b.pickUpDateTime not IN (:excluded)')->setParameter('excluded', $excludeStartDates);
}
$queryBuilder
->orderBy('b.pickUpDateTime', 'ASC');
$result = array_merge($result, $queryBuilder->getQuery()->getResult());
$bookings = array_unique($result, SORT_REGULAR);
if (!empty($bookings)) {
/** @var Booking $fBook */
foreach ($bookings as $key => $fBook) {
if ($fBook->getUnassignedHideAt() && $this->checkUnassignedDate(intval($day), $fBook)) {
unset($bookings[$key]);
continue;
}
if ($driver_bonus_feature_active == 1 && in_array($currentWeek, $driver_bonus_week_active) && empty($fBook->getDriverPrice())) {
if (!empty($fBook->getBookingDataSerialized())) {
$unserializedData = $fBook->getBookingDataSerializedDecoded();
// $priceWithoutPeriod = $unserializedData->priceWithoutPeriod;
$priceWithoutPeriod = $this->getEntityManager()->getRepository(Settings::class)->applyDriverRoundedBySettings(($unserializedData->price - abs($unserializedData->periodPriceStart)));
$bookingPriceWithoutPeriod = $priceWithoutPeriod;
$totalBPrice = $unserializedData->price;
$bPrice = $unserializedData->result->routePrice;
$rbPrice = $unserializedData->result->returnRoutePrice;
if ($rbPrice !== 0) {
$bookingPriceWithoutPeriod = $this->getEntityManager()->getRepository(Settings::class)->applyDriverRoundedBySettings((($bPrice * $priceWithoutPeriod) / $totalBPrice));
}
$fBook->setDriverPrice($bookingPriceWithoutPeriod);
}
}
// $estimated = explode(':', $fBook->getEstimatedTime());
// $bookingPickup = $fBook->getPickUpDateTime()->format('Y-m-d H:i');
// $ed = new \DateTime($fBook->getPickUpDateTime()->format('Y-m-d H:i'));
// $edu = new \DateTime($fBook->getPickUpDateTime()->format('Y-m-d H:i'));
// $stu = new \DateTime($fBook->getPickUpDateTime()->format('Y-m-d H:i'));
// $bookingEndEstimated = ($ed->add(new \DateInterval('PT' . $estimated[0] . 'H' . $estimated[1] . 'M')))->format('Y-m-d H:i');
// $eduEst = ($edu->add(new \DateInterval('PT' . $estimated[0] . 'H' . $estimated[1] . 'M')));
// $bookingStartUndispatchedPickup = ($stu->add(new \DateInterval('PT' . $undispatched_interval_between_bookings_pickup . 'H')))->format('Y-m-d H:i');
// $bookingEndUndispatchedDropoff = ($eduEst->add(new \DateInterval('PT' . $undispatched_interval_between_bookings_dropoff . 'H')))->format('Y-m-d H:i');
// foreach ($excludedBookingTimes as $exDate) {
// if (($bookingPickup >= $exDate['startDate'] and $bookingPickup <= $exDate['endDate']) or
// ($bookingEndEstimated >= $exDate['startDate'] and $bookingEndEstimated <= $exDate['endDate']) or
// ($bookingStartUndispatchedPickup >= $exDate['startDate'] and $bookingStartUndispatchedPickup <= $exDate['endDate']) or
// ($bookingEndUndispatchedDropoff >= $exDate['startDate'] and $bookingEndUndispatchedDropoff <= $exDate['endDate'])
// ) {
// unset($bookings[$key]);
// }
// }
$bookingPickup = $fBook->getPickUpDateTime();
$before = $undispatched_interval_between_bookings_pickup;
$after = $undispatched_interval_between_bookings_dropoff;
$startBefore = clone $bookingPickup;
$startAfter = clone $bookingPickup;
$endDate = clone $bookingPickup;
$startBefore = $startBefore->add(new \DateInterval('PT'. $before. 'H'));
$startAfter = $startAfter->sub(new \DateInterval('PT'. $after. 'H'));
$estimated = explode(':', $fBook->getEstimatedTime());
$estimatedInterval = new \DateInterval('PT'. $estimated[0]. 'H'. $estimated[1]. 'M');
$endDate->add($estimatedInterval);
$endBefore = clone $endDate;
$endAfter = clone $endDate;
$endBefore = $endBefore->add(new \DateInterval('PT'. $before. 'H'));
$endAfter = $endAfter->sub(new \DateInterval('PT'. $after. 'H'));
// Logic:
// If booking times (pickup or dropoff) are between:
// [
// $b hours (margin) before the assigned booking's pickup
// and
// $a hours (margin) after the assigned booking's dropoff
// ]
// Comp: $aS - $b <= $date <= $aE + $a
// Simplified: $aS <= $date + $b && $date - $a <= $aE
foreach ($excludedBookingTimes as $assigned) {
$aS = $assigned['startDate'];
$aE = $assigned['endDate'];
if (
// pickup is in between the assigned booking's dates with margins
($aS <= $startBefore && $startAfter <= $aE) ||
// dropoff is in between the assigned booking's dates with margins
($aS <= $endBefore && $endAfter <= $aE)
) {
unset($bookings[$key]);
}
}
// No point doing extra checks if booking was already removed.
if (isset($bookings[$key]) && $day == null) {
$driver_rating_max_limit = floatval($settingsRepo->findOneByKey('driver_rating_max_limit')->getValue());
$driver_current_rating = floatval($driver->getOverallRating());
if ($driver_current_rating < $driver_rating_max_limit) {
$vehicle_undispatched_max_price = (!empty($driver->getCar()->getType()) && !empty($driver->getCar()->getType()->getMaxPriceUndispatchedBooking())) ? floatval($driver->getCar()->getType()->getMaxPriceUndispatchedBooking()) : 0;
if ($vehicle_undispatched_max_price > 0) {
if (!empty($fBook->getDriverPrice())) {
if ($fBook->getDriverPrice() > $vehicle_undispatched_max_price) {
unset($bookings[$key]);
}
} else {
if (!empty($fBook->getOverridePrice())) {
if ($fBook->getOverridePrice() > $vehicle_undispatched_max_price) {
unset($bookings[$key]);
}
} else {
if ($fBook->getQuotePrice() > $vehicle_undispatched_max_price) {
unset($bookings[$key]);
}
}
}
}
}
}
}
}
return $bookings;
}
/**
* @param int $day
* @param Booking $booking
*
* @return bool
*/
private function checkUnassignedDate($day, Booking $booking)
{
$settingsRepo = $this->getEntityManager()->getRepository(Settings::class);
$bookingHide7daysOver = $settingsRepo->findOneByKey('live_linked_hide_booking_7d_over3');
$bookingHide7daysUnder = $settingsRepo->findOneByKey('live_linked_hide_booking_7d_under3');
$bookingHide30days = $settingsRepo->findOneByKey('live_linked_hide_booking_30d');
$now = new DateTime();
$unset = false;
$hideDiff = date_diff($now, $booking->getUnassignedHideAt());
$hoursDiff = $hideDiff->h + ($hideDiff->days * 24);
if ($day === 7) {
$dateDiff = date_diff($booking->getPickUpDateTime(), $now);
if (($dateDiff->days < 3 && $hoursDiff < $bookingHide7daysUnder)
|| ($dateDiff->days > 3 && $hoursDiff < $bookingHide7daysOver)
) {
$unset = true;
}
}
if ($day === 30 && $hoursDiff < $bookingHide30days) {
$unset = true;
}
return $unset;
}
public function countBetweenDatesByStatusAndDriver(Driver $driver, DateTime $startDate, DateTime $endDate, $status = null)
{
$qb = $this
->createQueryBuilder('b')
->select('count(b.id)')
;
if ($status) {
$qb
->andWhere('b.status = :status')
->setParameter('status', $status)
;
}
$qb
->andWhere('b.driver = :driver')
->andWhere($qb->expr()->between('b.pickUpDateTime', ':startDatePickUp', ':endDatePickUp'))
->setParameter('driver', $driver)
->setParameter('startDatePickUp', $startDate)
->setParameter('endDatePickUp', $endDate)
;
return intval($qb->getQuery()->getSingleScalarResult());
}
/**
* @param string $search
*
* @return array
*/
public function searchByKey(string $search = null)
{
$qb = $this
->createQueryBuilder('b')
->select('
b.key AS bookingKey, c.id AS clientId, c.firstname AS clientFirstName,
c.lastname AS clientLastName, c.phone AS clientPhone, c.email AS clientEmail
')
->leftJoin('b.clientUser', 'c')
->addSelect('
du.id AS driverId, du.firstname AS driverFirstName,
du.lastname AS driverLastName, du.phone AS driverPhone, du.email AS driverEmail
')
->leftJoin('b.driver', 'd')
->leftJoin('d.user', 'du')
;
if ($search) {
$qb
->andWhere('b.key LIKE :key')
->setParameter('key', "%{$search}%")
;
}
return $qb
->setMaxResults(15)
->getQuery()
->getArrayResult()
;
}
/**
* @param Driver $driver
* @param int $numberOfWeeks
*
* @return array
*/
public function countCompletedBookingsPastWeeksByDriverId(int $driverId, int $numberOfWeeks)
{
$endDatePickUp = new DateTime('last sunday');
$endDatePickUp->setTime(23, 59, 59);
$startDatePickUp = clone $endDatePickUp;
$startDatePickUp = $startDatePickUp->sub(new DateInterval("P{$numberOfWeeks}W"));
$startDatePickUp = $startDatePickUp->add(new DateInterval('P1D'));
$startDatePickUp->setTime(0, 0, 0);
$qb = $this->createQueryBuilder('b')->select('count(b.id)');
$qb
->innerJoin('b.driver', 'd')
->andWhere('d.id = :driverId')
->andWhere('b.status = :status')
->andWhere($qb->expr()->between('b.pickUpDateTime', ':startDatePickUp', ':endDatePickUp'))
->setParameter('driverId', $driverId)
->setParameter('status', Booking::STATUS_COMPLETED)
->setParameter('startDatePickUp', $startDatePickUp)
->setParameter('endDatePickUp', $endDatePickUp)
;
return intval($qb->getQuery()->getSingleScalarResult());
}
}

src/AdminBundle/Repository/BookingRepository.php line 875