/**
* @param RepeatedDrivingAssertionPlan $repeatedDrivingAssertionPlan
* @return array|mixed
*/
public function findAllProspectiveByRepeatedDrivingAssertionPlan(RepeatedDrivingAssertionPlan $repeatedDrivingAssertionPlan)
{
$now = new \DateTime();
$qb = parent::createQueryBuilder('e');
$qb->join('e.repeatedDrivingAssertionPlan', 'p')->join('e.shift', 's')->join('s.workingDay', 'w')->where('p = :assertionPlan')->andWhere('w.date >= :now')->setParameter('assertionPlan', $repeatedDrivingAssertionPlan)->setParameter('now', $now->format('Y-m-d'));
return $qb->getQuery()->getResult();
}
/**
* @return RepeatedDrivingAssertionPlan[]
*/
public function findAllActivePlansAtTheMoment()
{
$moment = new \DateTime();
$qb = parent::createQueryBuilder('p');
$qb->where('p.anchorDate <= :moment')->andWhere('p.endingDate >= :moment')->setParameter('moment', $moment->format('Y-m-d'));
return $qb->getQuery()->getResult();
}
/**
* @param PersonCategory $personCategory
* @return mixed
*/
public function getAmountByPersonCategory(PersonCategory $personCategory)
{
$qb = parent::createQueryBuilder('e')->select('count(e.id)');
$qb->innerJoin('e.personCategories', 'c', 'WITH', 'c = :personCategory');
$qb->setParameter('personCategory', $personCategory);
return $qb->getQuery()->getSingleScalarResult();
}
/**
* @return Route[]
*/
public function findRoutesOlderThenOneMonth()
{
$now = new \DateTime();
$pastMonth = $now->modify('-1 month');
$qb = parent::createQueryBuilder('r');
$qb->where('r.modifiedDateTime <= :pastMonth')->andWhere('r.isDeleted = 0')->setParameter('pastMonth', $pastMonth->format('Y-m-d'));
return $qb->getQuery()->getResult();
}
/**
* @param Handicap $handicap
* @return mixed
*/
public function getAmountByHandicap(Handicap $handicap)
{
$qb = parent::createQueryBuilder('e');
$qb->select('count(e.id)');
$qb->join('e.handicaps', 'h');
$qb->where('h = :handicap');
$qb->setParameter('handicap', $handicap);
return $qb->getQuery()->getSingleScalarResult();
}
/**
* Fetch all active repeated driving order plans and children:
* Repeated Driving Orders and Routes valid for a defined working day.
* All objects are hydrated.
*
* @param \DateTime $day
* @return mixed
*/
public function fetchAllForDate(\DateTime $day)
{
$anchorDay = $day->format('Y-m-d');
$endingDay = $anchorDay;
// same date!
$qb = parent::createQueryBuilder('rdop');
$qb->select('rdop, rdo, r')->leftJoin('rdop.repeatedDrivingOrders', 'rdo')->leftJoin('rdop.route', 'r')->where('rdop.anchorDate <= :anchorDay')->andWhere('rdop.endingDate >= :endingDay')->andWhere('rdop.isDeleted = 0')->setParameter('anchorDay', $anchorDay)->setParameter('endingDay', $endingDay);
return $qb->getQuery()->getResult();
}
/**
* @param ShiftType $shiftType
* @return mixed
*/
public function getAmountByShiftType(ShiftType $shiftType)
{
$qb = parent::createQueryBuilder('e');
$qb->select('count(e.id)');
$qb->join('e.shiftTypes', 'h');
$qb->where('h = :shiftType');
$qb->setParameter('shiftType', $shiftType);
return $qb->getQuery()->getSingleScalarResult();
}
/**
* @param POIKeyword $poiKeyword
* @return mixed
*/
public function getAmountByPOIKeyword(POIKeyword $poiKeyword)
{
$qb = parent::createQueryBuilder('e');
$qb->select('count(e.id)');
$qb->join('e.keywords', 'k');
$qb->where('k = :keyword');
$qb->setParameter('keyword', $poiKeyword);
return $qb->getQuery()->getSingleScalarResult();
}
/**
* @param $name
* @return bool
*/
public function checkIfNameAlreadyExist($name)
{
$qb = parent::createQueryBuilder('s');
$qb->select()->where('s.isDeleted = 0')->andWhere('s.name = :duplicateName')->setParameter('duplicateName', $name);
if ($qb->getQuery()->getResult()) {
return true;
} else {
return false;
}
}
/**
* @return mixed
*/
public function getAmountOfCompanyOwnedVehicles()
{
$qb = parent::createQueryBuilder('e')->select('count(e.id)')->where('e.companyOwned = 1')->andWhere('e.isDeleted = 0');
return $qb->getQuery()->getSingleScalarResult();
}
/**
* @param int $planId
* @return DrivingOrder[]
*/
public function findAllOrdersForPlan($planId)
{
$qb = parent::createQueryBuilder('o');
$qb->join('o.repeatedDrivingOrderPlan', 'p')->where('p.id = :planId')->setParameter('planId', $planId);
return $qb->getQuery()->getResult();
}
/**
* @return mixed
*/
public function findUnclassifiedZone()
{
$qb = parent::createQueryBuilder('z');
$qb->where('z.priority = :unclassifiedPriority')->setParameter('unclassifiedPriority', Zone::UNCLASSIFIEDPRIORITY);
return $qb->getQuery()->getOneOrNullResult();
}
/**
* @return Driver[]
*/
public function findAllActive()
{
$qb = parent::createQueryBuilder('e');
$qb->select()->where('e.isDeleted = 0');
return $qb->getQuery()->getResult();
}
/**
* Find all objects with the specified tag.
* @param $sessionTag
* @return QueueItem[]|null
*/
public function findQueueItemsByTag($sessionTag)
{
$qb = parent::createQueryBuilder('e');
$qb->where('e.sessionTag = :sessionTag')->andWhere('e.isDeleted = 0')->setParameter('sessionTag', $sessionTag);
return $qb->getQuery()->getResult();
}
/**
* @param $missionId
* @return mixed
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function findDrivingMissionWithChildrenForId($missionId)
{
$qb = parent::createQueryBuilder('dm');
$qb->select('dm, do, dp, v, r, a1, a2, p')->leftJoin('dm.drivingOrders', 'do')->leftJoin('do.route', 'r')->leftJoin('r.startAddress', 'a1')->leftJoin('r.targetAddress', 'a2')->leftJoin('do.passenger', 'p')->leftJoin('dm.drivingPool', 'dp')->leftJoin('dp.vehicle', 'v')->where('dm.id = :mid')->setParameter('mid', $missionId);
return $qb->getQuery()->getOneOrNullResult();
}
/**
* @return Address[]
*/
public function findAddressesWithoutNearestCoordinates()
{
$qb = parent::createQueryBuilder('a');
$qb->where('a.nearestLat = 0')->orWhere('a.nearestLat is NULL')->orWhere('a.nearestLng = 0')->orWhere('a.nearestLng is NULL');
return $qb->getQuery()->getResult();
}
/**
* Get an array of hydrated zone plans
* @return ZonePlan[]
*/
public function getActiveZonePlans()
{
$qb = parent::createQueryBuilder('e');
$qb->select('e')->where('e.isDeleted = 0');
return $qb->getQuery()->getResult();
}
/**
* @param \DateTime $date
* @return BankHoliday
*/
public function findBankHolidayForDate(\DateTime $date)
{
$qb = parent::createQueryBuilder('e');
$qb->where('e.date = :date')->andWhere('e.isDeleted = 0')->setParameter('date', $date->format('Y-m-d'));
return $qb->getQuery()->getOneOrNullResult();
}
/**
* Fetch the shift with driving pool id(X) and all hydrated child objects.
*
* @param int $drivingPoolId
* @return Shift|null
*/
public function getShiftForDrivingPoolId($drivingPoolId)
{
$qb = parent::createQueryBuilder('s');
$qb->select('s', 'dp')->innerJoin('s.drivingPools', 'dp')->where('dp.id = :dpId')->setParameter('dpId', $drivingPoolId);
return $qb->getQuery()->getOneOrNullResult();
}
/**
* Optimized for monthly plan (3.5x faster)
* @param integer $id
* @return null|WorkingDay plus child objects (fetched and hydrated)
*/
public function fetchWorkingDayPlusChildrenForId($id)
{
$qb = parent::createQueryBuilder('wd');
$qb->select('wd, s, da, d, dp, dm, v, vc')->leftJoin('wd.shifts', 's')->leftJoin('s.drivingAssertions', 'da')->leftJoin('da.driver', 'd')->leftJoin('s.drivingPools', 'dp')->leftJoin('dp.drivingMissions', 'dm')->leftJoin('dp.vehicle', 'v')->leftJoin('v.category', 'vc')->where('wd.id = :id')->orderBy('dp.id', 'ASC')->setParameter('id', $id);
return $qb->getQuery()->getOneOrNullResult();
}
/**
* @param \DateTime $date
* @param array $poolIds
* @return mixed
*/
public function fetchDistinctDrivingPools(\DateTime $date, $poolIds)
{
$qb = parent::createQueryBuilder('dp');
$qb->select('dp')->leftJoin('dp.shift', 's')->leftJoin('s.workingDay', 'wd')->andWhere('wd.date = :day')->andWhere('dp.id in (:ids)')->setParameter('day', $date->format('Y-m-d'))->setParameter('ids', $poolIds);
return $qb->getQuery()->getResult();
}
public function fetchMonthPlanOrphansForId($id)
{
$qb = parent::createQueryBuilder('wm');
$qb->select('wm, wd, s, da, d')->leftJoin('wm.workingDays', 'wd')->leftJoin('wd.shifts', 's')->leftJoin('s.drivingAssertions', 'da')->leftJoin('da.driver', 'd')->where('wm.id = :id')->orderBy('wd.id', 'ASC')->setParameter('id', $id);
return $qb->getQuery()->getOneOrNullResult();
}
