<?php
// $Id: index.php,v 1.6 2007-10-21 17:49:32 mastermind Exp $
// Frontpage
class Page_Index extends PgPage {
   function __construct() {
      $this->content_template = 'index.html';
   }
   function Render() {
      global $_SETTINGS;

      // Enable RSS links
      $this->tpl->touchBlock('page_rss_links');

      // Latest versions
      foreach ($_SETTINGS['versions'] as $major=>$verinfo) {
          if ($verinfo['frontpage'] != 1) continue;
          $this->tpl->setVariable('majorver', $major);
          $this->tpl->setVariable($verinfo);
          $this->tpl->parse('front_release_loop');
      }

      // Fetch a random quote
      $rs = $this->pg_query_params("SELECT COALESCE(qt1.quote,qt2.quote) AS quote, COALESCE(qt1.tagline,qt2.tagline) AS tagline FROM quotes q INNER JOIN quotes_text qt2 ON q.id=qt2.quoteid LEFT JOIN (SELECT quoteid,quote,tagline FROM quotes_text WHERE language=$1) qt1 ON qt1.quoteid=q.id WHERE qt2.language='en' AND q.active AND q.approved ORDER BY qt2.modified < now() - '8 days'::interval, random() LIMIT 1", array($this->language)); 
      if (pg_num_rows($rs)  == 1) {
         $this->tpl->setVariable(pg_fetch_array($rs, 0));
         $this->tpl->parse('quotes_index');
      }
      
      // Fetch news
      $rs = $this->pg_query_params("SELECT n.id, n.posted, n.posted_by, COALESCE(nt1.headline,nt2.headline) AS headline, COALESCE(nt1.summary,nt2.summary) AS summary, to_char(n.posted, 'dd-mm-YYYY') AS posted_fmt FROM news n INNER JOIN news_text nt2 ON n.id=nt2.newsid LEFT JOIN (SELECT newsid,headline,summary FROM news_text WHERE language=$1) nt1 ON n.id=nt1.newsid WHERE n.approved AND nt2.language='en' ORDER BY n.posted DESC LIMIT 6", array($this->language));
      for($i=0; $i < pg_num_rows($rs); $i++) {
         $this->tpl->setVariable(pg_fetch_array($rs, $i));
         $this->tpl->parse('news_index_loop');
      }

      // Fetch events
      $this->DoEvents('event',3);
      $this->DoTraining();

      // Fetch planet feed
      $rssparser = new RSSParser('../../dl/planetpostgresql.rss',
                       array());
      if ($rssparser->success()) {
   	   $this->tpl->touchBlock('planet_block');
   	   $n=0;
   	   foreach ($rssparser->getItems() as $i) {
	         $this->tpl->setVariable($i);
   		   $this->tpl->parse('planet_loop');
   		   if ($n++ > 3) break;
   	   }
      }
   }

   function DoEvents($what, $limit) {
      global $_SETTINGS;
      
      $train = ($what=='training')?'TRUE':'NOT TRUE';
      $rs = $this->pg_query_params("SELECT e.id, e.start_date, e.end_date, COALESCE(et1.event,et2.event) AS event, el.city, el.state, c.name AS country FROM events e INNER JOIN events_location el ON e.id=el.eventid INNER JOIN countries c ON el.country=c.id INNER JOIN events_text et2 ON e.id=et2.eventid LEFT JOIN (SELECT eventid,event FROM events_text WHERE language=$1) et1 ON e.id=et1.eventid WHERE e.start_date IS NOT NULL AND e.end_date >= CURRENT_DATE AND et2.language='en' AND e.approved AND e.training IS $train ORDER BY e.end_date,e.start_date LIMIT $limit", array($this->language));
      for ($i = 0; $i < pg_num_rows($rs); $i++) {
         $r = pg_fetch_assoc($rs, $i);
         if (isset($r['end_date']) && $r['end_date'] != $r['start_date']) {
            // both start and end date
            $this->tpl->setVariable(array(
               $what . '_date_start' => $r['start_date'],
               $what . '_date_end' => $r['end_date']
            ));
         }
         else {
            $this->tpl->setVariable($what . '_date', $r['start_date']);
         }

         $this->tpl->setVariable(array(
            $what . '_location' => $this->format_location($r),
            $what . '_id' => $r['id'],
            $what . '_event' => htmlentities($r['event'],ENT_COMPAT,'utf-8')
         ));
         $this->tpl->parse($what . '_index_loop');
      }
      $this->tpl->clearVariables();
   }

   function DoTraining() {
      global $_SETTINGS;

      $rs1 = $this->pg_query("SELECT count(*) AS training_event_count, count(DISTINCT el.country) AS training_country_count FROM events e, events_location el WHERE e.id = el.eventid AND start_date IS NOT NULL AND start_date <= (CURRENT_DATE + '6 Months'::interval) AND end_date >= CURRENT_DATE AND approved AND training = TRUE");
      $r = pg_fetch_assoc($rs1, 0);

      $rs2 = $this->pg_query("SELECT * FROM (SELECT DISTINCT(organisation) FROM events e, events_location el WHERE e.id = el.eventid AND start_date IS NOT NULL AND start_date <= (CURRENT_DATE + '6 Months'::interval) AND end_date >= CURRENT_DATE AND approved AND training = TRUE AND organisation IS NOT NULL) AS organisation ORDER BY random() LIMIT 3;");

      $num = pg_num_rows($rs2);
      $this->tpl->hideBlock('training_summary_many');

      if ($num == 0)
        $this->tpl->touchBlock('training_summary_none');
      else if ($num == 1) {
        $this->tpl->touchBlock('training_summary_1');
        $this->tpl->setVariable('training_company_1', pg_fetch_result($rs2, 0, 0));
      }
      else if ($num == 2 ) {
        $this->tpl->touchBlock('training_summary_2');
        $this->tpl->setVariable(array(
          'training_company_1' => pg_fetch_result($rs2, 0, 0),
          'training_company_2' => pg_fetch_result($rs2, 1, 0) 
        ));
      }
      else if ($num == 3) {
        $this->tpl->touchBlock('training_summary_many');
        $this->tpl->setVariable(array(
          'training_company_1' => pg_fetch_result($rs2, 0, 0),
          'training_company_2' => pg_fetch_result($rs2, 1, 0),
        ));
      }

      $this->tpl->setVariable(array(
        'training_event_count' => $r['training_event_count'],
        'training_country_count' => $r['training_country_count']
      ));
   }
}
?>