#include <lt2001.style>

<page title="Update Database" subtitle=db-Update>

<?
  function update_supply ($project, $supply, $amount)
  {
    global $dbh;

    $query = sprintf ("SELECT amount FROM supplies WHERE project = '%s' AND supply = '%s'", $project, $supply);
    $sth = pg_exec ($dbh, $query); # or die("Datenbank-Abfrage!");
    if (pg_NumRows ($sth) > 0) {
      $row = pg_fetch_array ($sth, 0);
    }

    if ($row['amount'] > 0) {
      if ($row['amount'] != $amount) {
        if ($amount > 0) {
	  $query = sprintf ("UPDATE supplies SET amount = %d WHERE project = '%s' AND supply = '%s'",
			    $amount, $project, $supply);
	} else {
	  $query = sprintf ("DELETE FROM supplies WHERE project = '%s' AND supply = '%s'", $project, $supply);
	}
      }
    } else {
      if ($amount > 0) {
        $query = sprintf ("INSERT INTO supplies (supply,project,amount,comment) VALUES ('%s','%s',%d,'')",
	                  $supply, $project, $amount);
      }
    }

    if (strlen ($query)) {
      $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
    }
  }

  if (!$type) die ("No type given");

  $dbh = pg_pconnect ("<dbhost>", "<dbport>", "<dbname>")
	       or die("Unable to connect to SQL server");

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

  if ($type == "project") {
    if ($delete == 1) {
      $query = sprintf ("DELETE FROM project WHERE oid = %d", $oid);
    } elseif ($oid == 0) {
      $query = sprintf ("INSERT INTO project (name,url,boothnr,boothsize,computer,comment) VALUES ('%s','%s','%s',%d,%d,'%s')",
		       $name, $url, $boothnr, $boothsize, $computer, $comment);
    } else {
      $query = sprintf ("UPDATE project SET name='%s',url='%s',boothnr='%s',"
		       ."boothsize=%d,computer=%d,comment='%s' WHERE oid = %d",
		       $name, $url, $boothnr, $boothsize, $computer, $comment, $oid);
    }
  } elseif ($type == "person") {
    if ($delete == 1) {
      $query = sprintf ("SELECT name FROM person WHERE oid = %d", $oid);
      $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
      if (pg_NumRows ($sth) > 0) {
        $row = pg_fetch_array ($sth, 0);
	$name = $row['name'];
      }
      $query = sprintf ("DELETE FROM person WHERE oid = %d", $oid);
    } elseif ($oid == 0) {
      $query = sprintf ("INSERT INTO person (name,email,project,nightplace,night,infomail,insurance,list,admin,"
		       ."login,password,comment) "
		       ."VALUES ('%s','%s','%s','%s',%d,%d,%d,%d,%d,%d,'%s','%s')",
		       $name, $email, $project, $nightplace, $night, $infomail, $insurance, $list,
		       $login, $admin, strlen ($password)?md5 ($password):"", $comment);
    } else {
      $query = sprintf ("UPDATE person SET name='%s',email='%s',project='%s',"
		       ."nightplace='%s',night=%d,infomail=%d,insurance=%d,list=%d,admin=%d,login=%d,comment='%s'",
		       $name, $email, $project, $nightplace, $night, $infomail, $insurance, $list, $admin, $login, $comment);
      if (strlen ($password)) {
        $query .= sprintf (",password='%s'", md5 ($password));
      }
      $query .= sprintf (" WHERE oid = %d", $oid);
    }
  } elseif ($type == "url") {
    if ($delete == 1) {
      $query = sprintf ("DELETE FROM urls WHERE oid = %d", $oid);
    } elseif ($oid == 0) {
      $query = sprintf ("INSERT INTO urls (project,url,description) "
		       ."VALUES ('%s','%s','%s')",
		       $project, $url, $description);
    } else {
      $query = sprintf ("UPDATE urls SET url='%s',description='%s' WHERE oid = %d",
		       $url, $description, $oid);
    }
  } elseif ($type == "appendix") {
    if ($delete == 1) {
      $query = sprintf ("DELETE FROM appendix WHERE oid = %d", $oid);
    } elseif ($oid == 0) {
      $query = sprintf ("INSERT INTO appendix (project,boothtitle,description,description_en) "
		       ."VALUES ('%s','%s','%s','%s')",
		       $project, $boothtitle, $description, $description_en);
    } else {
      $query = sprintf ("UPDATE appendix SET boothtitle='%s',description='%s',description_en='%s' WHERE oid = %d",
		       $boothtitle,$description,$description_en, $oid);
    }
  } elseif ($type == "comment") {
    $date = date ("Y-m-j H:i");
    if (!strlen ($who)) {
      $who = $REMOTE_USER;
      if ($who == "joey") {
        $who = "Martin Schulze";
      }
      $email = $REMOTE_USER . "@linuxtag.de";
   }
   if ($delete == 1) {
      $query = sprintf ("DELETE FROM comments WHERE oid = %d", $oid);
    } elseif ($oid == 0) {
      $query = sprintf ("INSERT INTO comments (project,date,who,email,body) "
		       ."VALUES ('%s','%s','%s','%s','%s')",
		       $project, $date, $who, $email, $body);
    } else {
      $query = sprintf ("UPDATE comments SET who='%s',email='%s',body='%s' "
		       ."WHERE oid = %d",
		       $who, $email, $body, $oid);
    }
  } elseif ($type == "todo") {
    $date = date ("Y-m-j H:i");
    if (!strlen ($who)) {
      $who = $REMOTE_USER;
      if ($who == "joey") {
        $who = "Martin Schulze";
      }
      $email = $REMOTE_USER . "@linuxtag.de";
   }
   if ($delete == 1) {
      $query = sprintf ("DELETE FROM todo WHERE oid = %d", $oid);
    } elseif ($oid == 0) {
      $query = sprintf ("INSERT INTO todo (subject,who,email,date,body,done) "
		       ."VALUES ('%s','%s','%s','%s','%s',NULL)",
		       $subject, $who, $email, $date, $body);
    } else {
      if ($done == 1) {
        $query = sprintf ("UPDATE todo SET subject='%s',who='%s',email='%s',body='%s',done='%s'"
		       ."WHERE oid = %d",
		       $subject, $who, $email, $body, $date, $oid);
      } else {
        $query = sprintf ("UPDATE todo SET subject='%s',who='%s',email='%s',body='%s'"
		       ."WHERE oid = %d",
		       $subject, $who, $email, $body, $oid);
      }
    }
  } elseif ($type == "workshop") {
   if ($delete == 1) {
      $query = sprintf ("DELETE FROM workshop WHERE oid = %d", $oid);
    } elseif ($oid == 0) {
      $query = sprintf ("INSERT INTO workshop (title,speaker,date_time,room,project,abstract,visible,comment) "
		       ."VALUES ('%s','%s','%s %s','%s','%s','%s',%d,'%s')",
		       $title, $speaker, $date, $time, $room, $project, $abstract, $visible, $comment);
    } else {
      $query = sprintf ("UPDATE workshop SET title='%s',speaker='%s',date_time='%s %s',"
		       ."room='%s',project='%s',abstract='%s',visible=%d,comment='%s' "
		       ."WHERE oid = %d",
		       $title, $speaker, $date, $time, $room, $project, $abstract, $visible, $comment, $oid);
    }
  } elseif ($type == "supplylist") {
   if ($delete == 1) {
      $query = sprintf ("DELETE FROM supplylist WHERE oid = %d", $oid);
    } elseif ($oid == 0) {
      $price = ereg_replace(",", ".", $price);
      $query = sprintf ("INSERT INTO supplylist (id,name,height,width,depth,img_src,img_height,img_width,price,visible,comment) "
		       ."VALUES ('%s','%s',%d,%d,%d,'%s',%d,%d,%s,%d,'%s')",
		       $id,$name,$height,$width,$depth,$img_src,$img_height,$img_width,$price,$visible,$comment);
    } else {
      $price = ereg_replace(",", ".", $price);
      $query = sprintf ("UPDATE supplylist SET id='%s',name='%s',height=%d,width=%d,depth=%d,"
		       ."img_src='%s',img_height=%d,img_width=%d,price=%s,visible=%d,comment='%s' "
		       ."WHERE oid = %d",
		       $id,$name,$height,$width,$depth,$img_src,$img_height,$img_width,$price,$visible,$comment, $oid);
    }
  } elseif ($type == "supplies") {
    $query = "SELECT id FROM supplylist";

    $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);

	update_supply ($project, $row['id'], $HTTP_POST_VARS[$row['id']]);
      }
    }

  } else {
    echo ("<p><b>Unknown type</b><p>");
  }

  if (strlen ($query)) {
    $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
  }

  # Postprocessing für Nili-Versorgung
  if ($type == "person") {
    if ($delete == 1) {
      $query = sprintf ("DELETE FROM nili WHERE person = '%s'", $name);
    } else {

      for ($day=4; $day < 10; $day++) {
        $oid = sprintf ("oid_07_%02d", $day);
        $breakfast = sprintf ("breakfast_07_%02d", $day);
        $dinner = sprintf ("dinner_07_%02d", $day);
        $sleeping = sprintf ("sleeping_07_%02d", $day);
	$query = '';

	if (${$oid} > 0) {
	  if ((${$breakfast} > 0) || (${$dinner} > 0) || (${$sleeping} > 0)) {
	    $query = sprintf ("UPDATE nili SET breakfast=%d,dinner=%d,sleeping=%d WHERE oid = %d",
			      ${$breakfast}, ${$dinner}, ${$sleeping}, ${$oid});
	  } else {
	    $query = sprintf ("DELETE FROM nili WHERE oid = %d", ${$oid});
	  }
	} else {
	  if ((${$breakfast} > 0) || (${$dinner} > 0) || (${$sleeping} > 0)) {
	    $query = sprintf ("INSERT INTO nili (person,date,breakfast,dinner,sleeping,comment) VALUES "
			   ."('%s','2001-07-%02d',%d,%d,%d,'')", $name, $day, ${$breakfast}, ${$dinner}, ${$sleeping});
	  }
	}
        if (strlen ($query)) {
          $sth = pg_exec ($dbh, $query) or die("Datenbank-Abfrage!");
        }
      }
    }
  }

?>

<p><b>Done</b><p>

</page>

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