public function findByGuideAndSpotDetail(\Buggl\MainBundle\Entity\EGuide $eguide, \Buggl\MainBundle\Entity\SpotDetail $spotDetail)
{
$sql = "SELECT itsd.* \n\t\t\t\t\tFROM itinerary_to_spot_detail itsd\n\t\t\t\t\tLEFT JOIN itinerary it\n\t\t\t\t\t\tON itsd.itinerary_id = it.id\n\t\t\t\t\tLEFT JOIN e_guide eg\n\t\t\t\t\t\tON it.e_guide_id = eg.id\n\t\t\t\t\tWHERE 1\n\t\t\t\t\t\tAND it.e_guide_id = ?\n\t\t\t\t\t\tAND itsd.spot_detail_id = ?\n\t\t\t\t\t";
$params = array($eguide->getId(), $spotDetail->getID());
$em = $this->getEntityManager();
$rsm = new ResultSetMappingBuilder($em);
$rsm->addRootEntityFromClassMetadata('BugglMainBundle:ItineraryToSpotDetail', 'itsd');
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameters($params);
$result = $query->getResult();
return $result;
}
public function getAllNotInLocalSecretsByType(\Buggl\MainBundle\Entity\EGuide $eGuide, LocalAuthor $author, $type, $offset = 0, $limit = 0)
{
$typeClause = !is_null($type) ? 'AND sd.spot_type_id = ?' : '';
$sql = "SELECT sd . *\n\t\t\t\t\tFROM `spot_detail` AS sd\n\t\t\t\t\tWHERE 1\n\t\t\t\t\t\tAND sd.local_author_id = ?\n\t\t\t\t\t\tAND sd.id NOT IN (\n\t\t\t\t\t\t\tSELECT egts.spot_detail_id\n\t\t\t\t\t\t\tFROM e_guide_to_spot_detail AS egts\n\t\t\t\t\t\t\tWHERE egts.e_guide_id = ?\n\t\t\t\t\t\t)\n\t\t\t\t\t\t{$typeClause}";
// echo $sql;
$params = array($author->getId(), $eGuide->getId());
if (!is_null($type)) {
$params = array_merge($params, array($type->getId()));
}
if ($limit > 0) {
$sql .= " LIMIT ?,?";
$params = array_merge($params, array($offset, $limit));
}
$spotDetails = $this->executeNativeQuery($sql, $params);
return $spotDetails;
}
public function getPreviousTimeOfDay(\Buggl\MainBundle\Entity\EGuide $eGuide, $day = 0, $currentTime = 1)
{
$sql = "SELECT egts.* \n\t\t\t\t\tFROM e_guide_to_spot egts\n\t\t\t\t\tWHERE 1\n\t\t\t\t\t\tAND egts.e_guide_id = ? \n\t\t\t\t\t\tAND egts.day_num = ?\n\t\t\t\t\t\tAND egts.period_of_day < ?\n\t\t\t\t\tORDER BY egts.day_num ASC, egts.period_of_day DESC\n\t\t\t\t\tLIMIT 0, 1";
$params = array($eGuide->getId(), $day, $currentTime);
$em = $this->getEntityManager();
$rsm = new ResultSetMappingBuilder($em);
$rsm->addRootEntityFromClassMetadata('BugglMainBundle:EGuideToSpot', 'egts');
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameters($params);
try {
$result = $query->getSingleResult();
} catch (\Doctrine\ORM\NoResultException $e) {
return null;
}
return $result;
}
