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 getLastOrderByGuide(\Buggl\MainBundle\Entity\EGuide $eGuide)
{
$sql = "SELECT sd.* \n\t\t\t\t\tFROM e_guide_to_spot_detail sd\n\t\t\t\t\tWHERE 1\n\t\t\t\t\t\tAND sd.e_guide_id = ?\n\t\t\t\t\t\tORDER BY sd.order DESC\n\t\t\t\t\t\tLIMIT 0, 1";
$params = array($eGuide->getID());
$em = $this->getEntityManager();
$rsm = new ResultSetMappingBuilder($em);
$rsm->addRootEntityFromClassMetadata('BugglMainBundle:EGuideToSpotDetail', 'sd');
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameters($params);
try {
$result = $query->getResult();
} catch (\Doctrine\ORM\NoResultException $e) {
return null;
}
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;
}
private function createHtml(\Buggl\MainBundle\Entity\EGuide $eguide, $filename = null, $domain = null)
{
if (is_null($domain)) {
$url = "http://" . $_SERVER['HTTP_HOST'] . "/guide-preview/" . $eguide->getID();
} else {
$url = "http://" . $domain . "/guide-preview/" . $eguide->getID();
}
// echo "<br/>html preview : ".$url."<br/>";
$htmlDocRoot = $this->rootDir . '/../web/uploads/eguide_html';
if (!is_writable($htmlDocRoot)) {
mkdir("{$htmlDocRoot}", 0755);
}
$slug = str_replace($this->removeChars, '', $eguide->getSlug());
$filename = is_null($filename) ? $slug . ".html" : $filename;
$htmlFile = $htmlDocRoot . "/" . $filename;
if (!file_exists($htmlFile)) {
$ch = curl_init($url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$data = curl_exec($ch);
curl_close($ch);
file_put_contents($htmlFile, $data);
}
}
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;
}
