#include <lt2003.style>
#include "phpfunctions.inc"

<spokendate>
<?

function search_projects ($dbh, $key)
{
  global $foundsomething;

  $query = sprintf ("SELECT oid,name FROM project WHERE "
		   ."name ~* '%s' OR url ~* '%s' OR comment ~* '%s'",
		    $key, $key, $key);
  $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");

  if (pg_NumRows ($sth) > 0) {
    for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
      $row = pg_fetch_array ($sth, $nr);
      $result[$row['name']] = $row['oid'];
    }
  }
  
  $query = sprintf ("SELECT project.oid,name FROM project,appendix WHERE "
		   ."project = name AND ( "
		   ."boothtitle ~* '%s' OR description ~* '%s' OR description_en ~* '%s')",
		    $key, $key, $key);
  $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");

  if (pg_NumRows ($sth) > 0) {
    for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
      $row = pg_fetch_array ($sth, $nr);
      $result[$row['name']] = $row['oid'];
    }
  }

  if (count ($result) > 0) {
    if ($foundsomething == false) {
      $foundsomething = true;
      echo "<ol>";
    }

    while (list($k,$v) = each ($result)) {
      printf ("<li> Project <a href=\"projects/show.php3?oid=%d\">%s</a>",
	      $v, $k);
    }
  }
} // function search_projects ($dbh, $key)


function search_person ($dbh, $key)
{
  global $foundsomething;

  $query = sprintf ("SELECT oid,name,email,project FROM person WHERE "
		   ."name ~* '%s' OR email ~* '%s' OR comment ~* '%s' "
		   ."ORDER BY name",
		    $key, $key, $key);
  $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");

  if (pg_NumRows ($sth) > 0) {
    for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
      $row = pg_fetch_array ($sth, $nr);
      if (strlen ($row['email'])) {
        $this = $row['name'] . " &lt;" . $row['email'] . "&gt;";
      } else {
        $this = $row['name'];
      }
      $result[$this] = $row['oid'];
      $project[$this] = $row['project'];
    }
  }
  
  if (count ($result) > 0) {
    if ($foundsomething == false) {
      $foundsomething = true;
      echo "<ol>";
    }

    while (list($k,$v) = each ($result)) {
      printf ("<li> Person <a href=\"projects/edit_person.php3?context=%s&oid=%d\">%s</a>",
	      urlencode ($project[$k]),
	      $v, $k);
    }
  }
} // function search_person ($dbh, $key)

function search_comment ($dbh, $key)
{
  global $foundsomething;

  $query = sprintf ("SELECT oid,who,email,date FROM comments WHERE "
		   ."who ~* '%s' OR email ~* '%s' OR body ~* '%s' "
		   ."ORDER BY date,who",
		    $key, $key, $key);
  $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");

  if (pg_NumRows ($sth) > 0) {
    for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
      $row = pg_fetch_array ($sth, $nr);
      if (strlen ($row['email'])) {
        $this = $row['who'] . " &lt;" . $row['email'] . "&gt;";
      } else {
        $this = $row['who'];
      }
      $result[$this] = $row['oid'];
      $foo = explode (" ", $row['date']);
      $date[$this] = spokendate ($foo[0]);
    }
  }
  
  if (count ($result) > 0) {
    if ($foundsomething == false) {
      $foundsomething = true;
      echo "<ol>";
    }

    while (list($k,$v) = each ($result)) {
      printf ("<li> Comment from %s by <a href=\"projects/edit_comment.php3?oid=%d\">%s</a>",
	      $date[$k],
	      $v, $k);
    }
  }
} // function search_comment ($dbh, $key)

function search_workshop ($dbh, $key)
{
  global $foundsomething;

  $query = sprintf ("SELECT oid,title FROM workshop WHERE "
		   ."title ~* '%s' OR speaker ~* '%s' OR abstract ~* '%s' OR comment ~* '%s' "
		   ."ORDER BY title",
		    $key, $key, $key, $key);
  $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");

  if (pg_NumRows ($sth) > 0) {
    for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
      $row = pg_fetch_array ($sth, $nr);
      $result[$row['title']] = $row['oid'];
    }
  }
  
  if (count ($result) > 0) {
    if ($foundsomething == false) {
      $foundsomething = true;
      echo "<ol>";
    }

    while (list($k,$v) = each ($result)) {
      printf ("<li> Workshop <a href=\"workshops/edit.php3?oid=%d\">%s</a>",
	      $v, $k);
    }
  }
} // function search_workshop ($dbh, $key)

function search_job ($dbh, $key)
{
  global $foundsomething;

  $query = sprintf ("SELECT id,title FROM job WHERE "
		   ."title ~* '%s' OR location ~* '%s' OR description ~* '%s' OR comment ~* '%s' "
		   ."ORDER BY title",
		    $key, $key, $key, $key);
  $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");

  if (pg_NumRows ($sth) > 0) {
    for ($nr=0; $nr < pg_NumRows ($sth); $nr++) {
      $row = pg_fetch_array ($sth, $nr);
      $result[$row['title']] = $row['id'];
    }
  }
  
  if (count ($result) > 0) {
    if ($foundsomething == false) {
      $foundsomething = true;
      echo "<ol>";
    }

    while (list($k,$v) = each ($result)) {
      printf ("<li> Job <a href=\"supporter/job.php3?id=%d\">%s</a>",
	      $v, $k);
    }
  }
} // function search_job ($dbh, $key)

?>

<page title="Search Results">

<?
  $foundsomething = false;

  $dbh = pg_pconnect ("<dbconnstring>")
	       or die("Unable to connect to SQL server");

  # pg_exec ($dbh, "SET DateStyle = 'ISO'") or die("Datenbank-Abfrage!");

  if (strlen ($key)) {

    search_projects ($dbh, $key);
    search_person ($dbh, $key);
    search_comment ($dbh, $key);
    search_workshop ($dbh, $key);
    search_job ($dbh, $key);

    if ($foundsomething == true) {
      echo "</ol>";
    } else {
      printf ("<p>Nothing found matching <em>%s</em>.</p>", $key);
    }
  } else {
      echo "Forgot what to seach for, eh?";
  }

?>


</page>

# Local variables:
# mode: text
# mode: auto-fill
# end: