<?php
if (!function_exists('time_mysql2german')) {
function time_mysql2german($date) {
$d = explode(" ",$date);
$time = explode(':', $d[1]);
return sprintf("%02d:%02d", $time[0], $time[1]);
}
}

JLoader::register('fieldattach', 'components/com_fieldsattach/helpers/fieldattach.php');

$nullDate = $db->getNullDate();
$date = JFactory::getDate();
$now = $date->toSql();

$db = JFactory::getDbo();
require_once JPATH_BASE . '/phpQuery-onefile.php';

$nullDate = $db->getNullDate();
$date = JFactory::getDate();
$now = $date->toSql();
?>
<div class="Listing News-Tipps">

<?php
/*
* tv tipps are events (why? who knows...) therefore we have to get the stuff from different tables
*/
//first get all event IDs for exhibits
$query = $db->getQuery(true)
->select(
array(
$db->quoteName('rse.id'),
$db->quoteName('rse.start'),
$db->quoteName('rse.name'),
$db->quoteName('rse.description'),
$db->quoteName('rse.icon'),
)
);
$query->from($db->quoteName('#__rseventspro_taxonomy', 'rst'));
$query->join('INNER', $db->quoteName('#__rseventspro_events', 'rse') . ' ON rst.ide = rse.id');
$query->where($db->quoteName('rst.id') . ' = 404');
$query->where($db->quoteName('rst.type') . ' = \'category\'');
$query->where($db->quoteName('rse.start') . ' >= ' . $db->quote($now) . '');
$query->order('rse.start ASC LIMIT 3');

$db->setQuery($query);
// echo $db->getQuery();
$result_events = $db->loadObjectList();

if (count ($result_events) > 0)
{
?>
<div class="Article">
<h1>TV- & Radio-Tipps</h1>
</div>
<ul>
<?php
require_once JPATH_SITE.'/components/com_rseventspro/helpers/rseventspro.php';
// Load Router Helper
require_once JPATH_SITE.'/components/com_rseventspro/helpers/route.php';

foreach ($result_events as $result) {
// echo '<pre>';
// var_dump($result);
// echo '</pre>';
$src = '';
$link = rseventsproHelper::route('index.php?option=com_rseventspro&layout=show&id='.rseventsproHelper::sef($result->id,$result->name));
$teaser = substr($result->description, 0, 356);
$pq = phpQuery::newDocumentHTML($teaser);
$img = $pq->find('img:first');

if ($result->icon != '')
$src = JURI::root() . 'components/com_rseventspro/assets/images/events/' . $result->icon;
else
$src = $img->attr('src');

$pq->find('div')->remove();
$pq->find('img')->remove();
?>
<li class="Img">
<div class="Image"><a href="/<?php echo $link; ?>"><img alt="" src="/<?php echo $src; ?>"></a></div>
<div class="Wrap">
<div class="Date"><?php echo JHtml::_('date', $result->start, JText::_('DATE_FORMAT_LC4')); ?>, <?php echo time_mysql2german($result->start); ?></div>
<h3><a href="/<?php echo $link; ?>"><?php echo $result->name; ?></a></h3>
<div class="Teaser"><?php echo $pq; ?></div><!-- .Teaser -->
<div class="More"><a title="<?php echo $result->name; ?>" href="/<?php echo $link; ?>">Weiterlesen</a></div>
</div><!-- .Wrap -->
<div class="Clear"></div>
</li>
<?php
}
?>
</ul>
<div class="More"><a href="/index.php/de/startseite-de/1019561-news-tipps/1000046-tv-radio-tipps">Alle TV- & Radio-Tipps</a></div>

<?php
}
?>

<?php
$query = $db->getQuery(true);
$case_when = ' CASE WHEN ';
$case_when .= $query->charLength('a.alias', '!=', '0');
$case_when .= ' THEN ';
$a_id = $query->castAsChar('a.id');
$case_when .= $query->concatenate(array($a_id, 'a.alias'), ':');
$case_when .= ' ELSE ';
$case_when .= $a_id . ' END as slug';

$case_when1 = ' CASE WHEN ';
$case_when1 .= $query->charLength('c.alias', '!=', '0');
$case_when1 .= ' THEN ';
$c_id = $query->castAsChar('c.id');
$case_when1 .= $query->concatenate(array($c_id, 'c.alias'), ':');
$case_when1 .= ' ELSE ';
$case_when1 .= $c_id . ' END as catslug';

$query->select('a.id AS id, a.publish_up as created, a.alias as alias, a.title AS title, a.introtext AS introtext, a.images AS images')
->select($query->concatenate(array('a.introtext', 'a.fulltext')) . ' AS text')
->select('c.title AS section, ' . $case_when . ',' . $case_when1 . ', ' . '\'2\' AS browsernav');

$query->from('#__content AS a');
$query->join('INNER', '#__categories AS c ON c.id=a.catid');

$query->where('(' . $db->quoteName('catid') . ' = '. $db->quote('24') . ' OR ' .
$db->quoteName('catid') . ' LIKE '. $db->quote('24,%') . ' OR ' .
$db->quoteName('catid') . ' LIKE '. $db->quote('%,24') . ' OR ' .
$db->quoteName('catid') . ' LIKE '. $db->quote('%,24,%') . ')'
);

$query->where($db->quoteName('a.state') . ' = '. $db->quote('1'));
$query->order('a.publish_up DESC LIMIT 3');
$db->setQuery($query);
// echo $db->getQuery();
$results = $db->loadObjectList();
if (count ($results) > 0)
{
?>
<div class="Article">
<h1>Buchtipps</h1>
</div>
<ul>
<?php
foreach ($results as $result) {
$src = '';
$teaser = '';
if (fieldattach::getValue($result->id, 2, false) != '') :
$teaser = fieldattach::getValue($result->id, 2, false);
endif;

if ($teaser == ''){
if (fieldattach::getValue($result->id, 1, false) != '')
$teaser = fieldattach::getValue($result->id, 1, false);
}

$pq = phpQuery::newDocumentHTML($result->introtext);
$images = json_decode($result->images);
if (is_object($images)) {
if ($images->image_intro != '') {
$src = $images->image_intro;
}
}
if ($src == '') {
$img = $pq->find('img:first');
$src = $img->attr('src');
}
$pq->find('div')->remove();
if ($teaser == ''){
$teaser = substr($pq, 0, 356);
}

$link = JRoute::_(ContentHelperRoute::getArticleRoute( $result->slug, $result->catslug ));
?>
<li class="Img">
<div class="Image"><a href="/<?php echo $link; ?>"><img alt="" src="/<?php echo $src; ?>"></a></div>
<div class="Wrap">
<div class="Date"><?php echo JHtml::_('date', $result->created, JText::_('DATE_FORMAT_LC4')); ?></div>
<h3><a href="/<?php echo $link; ?>"><?php echo $result->title; ?></a></h3>
<div class="Teaser"><?php echo $teaser; ?></div><!-- .Teaser -->
<div class="More"><a title="<?php echo $result->title; ?>" href="/<?php echo $link; ?>">Weiterlesen</a></div>
</div><!-- .Wrap -->
<div class="Clear"></div>
</li>
<?php
}
?>
</ul>
<div class="More"><a href="/index.php/de/startseite-de/1019561-news-tipps/1001077-buchtipps">Alle Buchtipps</a></div>
<?php
}
?>

<?php
$query = $db->getQuery(true);

$query->select('a.id AS id, a.publish_up as created, a.alias as alias, a.title AS title, a.introtext AS introtext, a.images')
->select($query->concatenate(array('a.introtext', 'a.fulltext')) . ' AS text')
->select('c.title AS section, ' . $case_when . ',' . $case_when1 . ', ' . '\'2\' AS browsernav');

$query->from('#__content AS a');
$query->join('INNER', '#__categories AS c ON c.id=a.catid');

$query->where('(' . $db->quoteName('catid') . ' = '. $db->quote('67') . ' OR ' .
$db->quoteName('catid') . ' LIKE '. $db->quote('67,%') . ' OR ' .
$db->quoteName('catid') . ' LIKE '. $db->quote('%,67') . ' OR ' .
$db->quoteName('catid') . ' LIKE '. $db->quote('%,67,%') . ')'
);

$query->where($db->quoteName('a.state') . ' = '. $db->quote('1'));
// $query->where($db->quoteName('a.publish_up') . ' >= '. $db->quote($now));
$query->order('a.publish_up DESC LIMIT 3');
$db->setQuery($query);
// echo $db->getQuery();
$results = $db->loadObjectList();
if (count ($results) > 0)
{
?>
<div class="Article">
<h1>News</h1>
</div>
<ul>
<?php
foreach ($results as $result) {
$src = '';
$teaser = substr($result->introtext, 0, 356);
$pq = phpQuery::newDocumentHTML($teaser);
$images = json_decode($result->images);
if (is_object($images)) {
if ($images->image_intro != '') {
$src = $images->image_intro;
}
}
if ($src == '') {
$img = $pq->find('img:first');
$src = $img->attr('src');
}
$pq->find('div')->remove();
$link = JRoute::_(ContentHelperRoute::getArticleRoute( $result->slug, $result->catslug ));
?>
<li class="Img">
<div class="Image"><a href="/<?php echo $link; ?>"><img alt="" src="/<?php echo $src; ?>"></a></div>
<div class="Wrap">
<div class="Date"><?php echo JHtml::_('date', $result->created, JText::_('DATE_FORMAT_LC4')); ?></div>
<h3><a href="/<?php echo $link; ?>"><?php echo $result->title; ?></a></h3>
<div class="Teaser"><?php echo $pq; ?></div><!-- .Teaser -->
<div class="More"><a title="<?php echo $result->title; ?>" href="/<?php echo $link; ?>">Weiterlesen</a></div>
</div><!-- .Wrap -->
<div class="Clear"></div>
</li>
<?php
}
?>
</ul>
<div class="More"><a href="/index.php/de/startseite-de/1019561-news-tipps/1000177-news">Alle News</a></div>
<?php
}
?>

<?php
$query = $db->getQuery(true);

$query->select('a.id AS id, a.publish_up as created, a.alias as alias, a.title AS title, a.introtext AS introtext')
->select($query->concatenate(array('a.introtext', 'a.fulltext')) . ' AS text')
->select('c.title AS section, ' . $case_when . ',' . $case_when1 . ', ' . '\'2\' AS browsernav');

$query->from('#__content AS a');
$query->join('INNER', '#__categories AS c ON c.id=a.catid');

$query->where('(' . $db->quoteName('catid') . ' = '. $db->quote('57') . ' OR ' .
$db->quoteName('catid') . ' LIKE '. $db->quote('57,%') . ' OR ' .
$db->quoteName('catid') . ' LIKE '. $db->quote('%,57') . ' OR ' .
$db->quoteName('catid') . ' LIKE '. $db->quote('%,57,%') . ')'
);

$query->where($db->quoteName('a.state') . ' = '. $db->quote('1'));
$query->order('created DESC LIMIT 3');
$db->setQuery($query);
$results = $db->loadObjectList();
if (count ($results) > 0)
{
?>
<div class="Article">
<h1>Stellenausschreibungen</h1>
</div>
<ul>
<?php
foreach ($results as $result) {
$src = '';
$teaser = substr($result->introtext, 0, 356);
$pq = phpQuery::newDocumentHTML($teaser);
$images = json_decode($result->images);
if (is_object($images)) {
if ($images->image_intro != '') {
$src = $images->image_intro;
}
}
if ($src == '') {
$img = $pq->find('img:first');
$src = $img->attr('src');
}
$pq->find('div')->remove();
$link = JRoute::_(ContentHelperRoute::getArticleRoute( $result->slug, $result->catslug ));
?>
<li class="Img">
<div class="Image"><a href="/<?php echo $link; ?>"><img alt="" src="/<?php echo $src; ?>"></a></div>
<div class="Wrap">
<div class="Date"><?php echo JHtml::_('date', $result->created, JText::_('DATE_FORMAT_LC4')); ?></div>
<h3><a href="/<?php echo $link; ?>"><?php echo $result->title; ?></a></h3>
<div class="Teaser"><?php echo $pq; ?></div><!-- .Teaser -->
<div class="More"><a title="<?php echo $result->title; ?>" href="/<?php echo $link; ?>">Weiterlesen</a></div>
</div><!-- .Wrap -->
<div class="Clear"></div>
</li>
<?php
}
?>
</ul>
<div class="More"><a href="/index.php/de/startseite-de/1019561-news-tipps/1010234-stellenausschreibungen">Alle Stellenausschreibungen</a></div>
<?php
}
?>

</div>