<?php  /* -*- Mode: C++; indent-tabs-mode: nil; c-basic-indent: 2 -*- */
/*
 * Slooze PHP Web Photo Album
 * Copyright (c) 2000 Slooze developers (see AUTHORS file)
 * $Id: slooze_ct_sql.php,v 1.11 2001/01/15 00:47:04 mdkendall Exp $
 *
 * Container (Ct) class holding the photo data. Exactly where the data is
 * stored depends on which Ct class you use. In this implementation, the data
 * is held in an RDBMS reached via an encapsulating class.
 *
 * Function naming convention:
 * getBars()          SELECT * FROM Bars
 * getBarsInFoo($foo) SELECT * FROM Bars WHERE (Foo = $foo)
 * getBar($barID)     SELECT * FROM Bars WHERE (BarID = $barID) BarID is primary key
 *
 * getBars...() returns $bar[rows][fields]
 * getBar()     returns $bar[fields]
 *
 * addBar($bar)       INSERT INTO Bars VALUES $bar
 * updateBar($bar)    UPDATE Bars SET * = $bar WHERE (BarID = $bar[BarID])
 * deleteBar($barID)  DELETE FROM Bars WHERE (BarID = $barID)
 *
 */

class SloozeCtSql {

  /* Redefine this parameter by deriving your own class */
  var $sqlClass = "DB_Sql";  /* type of RDBMS data store */
  var $db;                   /* database access object */
  var $errString;            /* if an error occurs, set this to the error */
  
  /* public: constructor */
  function SloozeCtSql() {
    $name = $this->sqlClass;
    $this->db = new $name;
  }

  /* public: resetError() makes errString empty so errors don't carry over */
  function resetError() {
    $this->errString = "";
  }

  /* public: getError() returns errString */
  function getError() {
    return $this->errString;
  }

  /* public: getTopics() returns all the topics. */
  function getTopics() {
    $this->db->query("select * from Topics");
    return( $this->getAllRecords() );
  }

  /* public: getTopicsInParentTopic() returns all the topics with the
   * given parent TopicID. Slash / is a magic TopicID meaning root. */
  function getTopicsInParentTopic( $parentTopicID = "/" ) {
    $this->db->query("select * from Topics where ((ParentTopicID = '$parentTopicID') and (TopicID != '/'))");
    return( $this->getAllRecords() );
  }

  /* public: getTopic() returns a single topic. */
  function getTopic( $topicID ) {
    $this->db->query("select * from Topics where (TopicID = '$topicID')");
    return( $this->getSingleRecord() );
  }

  /* public: addTopic() adds one single topic */
  function addTopic( $topic ) {
    $cols = $this->makeCols($topic);
    $vals = $this->makeVals($topic);
    $this->db->query("insert into Topics ($cols) values ($vals)");
    return TRUE;
  }

  /* public: deleteTopic() deletes one single topic */
  function deleteTopic( $topicID ) {
    if (count($this->getPicturesInTopic($topicID)) == 0) {
      $this->db->query("delete from Topics where (TopicID = '$topicID')");
      return TRUE;
    }
    $this->errString = "Cannot delete: there are pictures in this topic.";
    return FALSE; /* failed */
  }

  /* public: updateTopic() updates one single topic */
  function updateTopic( $topic ) {
    $update = $this->makeUpdate($topic);
    $this->db->query("update Topics set $update where (TopicID = '".$topic['TopicID']."' )");
    return TRUE;
  }

  /* public: getRolls() returns all the rolls. */
  function getRolls() {
    $this->db->query("select * from Rolls");
    return( $this->getAllRecords() );
  }

  /* public: addRoll() adds one single roll */
  function addRoll( $roll ) {
    $cols = $this->makeCols($roll);
    $vals = $this->makeVals($roll);
    $this->db->query("insert into Rolls ($cols) values ($vals)");
    return TRUE;
  }

  /* public: deleteRoll() deletes one single roll */
  function deleteRoll( $rollID ) {
    if (count($this->getPicturesInRoll($rollID)) == 0) {
      $this->db->query("delete from Rolls where (RollID = '$rollID')");
      return TRUE;
    }
    $this->errString = "Cannot delete: there are pictures in this roll.";
    return FALSE; /* failed */
  }

  /* public: updateRoll() updates one single roll */
  function updateRoll( $roll ) {
    $update = $this->makeUpdate($roll);
    $this->db->query("update Rolls set $update where (RollID = '".$roll['RollID']."' )");
    return TRUE;
  }

  /* public: getPictures() returns all the pictures. */
  function getPictures() {
    $this->db->query("select * from Pictures");
    return( $this->getAllRecords() );
  }

  /* public: getPicturesInRoll() returns all the Pictures in a given Roll */
  function getPicturesInRoll( $rollID ) {
    $this->db->query("select * from Pictures where (RollID = '$rollID')");
    return( $this->getAllRecords() );
  }

  /* getPicturesInTopic() returns all the Pictures in a given Topic. */
  function getPicturesInTopic( $topicID ) {
    $this->db->query("select * from Pictures where (ParentTopicID = '$topicID')");
    return( $this->getAllRecords() );
  }

  /* getPicturesInSearch() returns all the Pictures where the given
   * search term occurs in the Description. */
  function getPicturesInSearch( $search ) {
    $this->db->query("select * from Pictures where (Description like '%$search%')");
    return( $this->getAllRecords() );
  }

  /* getPicture() returns a single picture. */
  function getPicture( $rollID, $frameID ) {
    $this->db->query("select * from Pictures where (RollID = '$rollID' and FrameID = '$frameID')");
    return( $this->getSingleRecord() );
  }

  /* public: addPicture() adds one single picture */
  function addPicture( $picture ) {
    $cols = $this->makeCols($picture);
    $vals = $this->makeVals($picture);
    $this->db->query("insert into Pictures ($cols) values ($vals)");
    return TRUE;
  }
  
  /* public: deletePicture() deletes one single picture */
  function deletePicture( $rollID, $frameID ) {
    $this->db->query("delete from Pictures where (RollID = '$rollID' and FrameID = '$frameID')");
    return TRUE;
  }

  /* public: updatePicture() updates one single picture */
  function updatePicture( $picture ) {
    $update = $this->makeUpdate($picture);
    $this->db->query("update Pictures set $update where (RollID = '" .
      $picture['RollID']."' and FrameID = '".$picture['FrameID']."' )");
    return TRUE;
  }
 
  /* public: incrementPictureViews() add one to Views field of picture */
  function incrementPictureViews( $rollID, $frameID, $picture ) {
    $this->db->query("update Pictures set Views = (Views + 1) where (RollID = '$rollID' and FrameID = '$frameID')");
    return TRUE;
  }
  
  /* public: getComments() returns all the comments. */
  function getComments() {
    $this->db->query("select * from Comments");
    return( $this->getAllRecords() );
  }
  
  /* getCommentsInPicture() returns all the Comments for the given picture. */
  function getCommentsInPicture( $rollID, $frameID ) {
    $this->db->query("select * from Comments where (RollID = '$rollID' and FrameID = '$frameID')");
    return( $this->getAllRecords() );
  }

  /* public: addComment() adds one single comment */
  function addComment( $comment ) {
    $cols = $this->makeCols($comment);
    $vals = $this->makeVals($comment);
    $this->db->query("insert into Comments ($cols) values ($vals)");
    return TRUE;
  }

  /* public: deleteComment() deletes one single comment */
  function deleteComment( $commentID ) {
    $this->db->query("delete from Comments where (CommentID = '$commentID')");
    return TRUE;
  }

  /* public: addRating() adds a rating */
  function addRating( $rating ) {
    $rollID = $rating['RollID'];
    $frameID = $rating['FrameID'];
    /* delete all recent ratings for this picture from this IP */
    $this->db->query("delete from Ratings where (RollID = '$rollID' and FrameID = '$frameID' " .
    "and IP = '" . $rating['IP'] . "' and RateTime > " . (time()-(30*60)) . ")");
    /* add the new rating */
    $cols = $this->makeCols($rating);
    $vals = $this->makeVals($rating);
    $this->db->query("insert into Ratings ($cols) values ($vals)");
    /* calculate the new rating for the picture */
    $this->db->query("select sum(Rating), count(*) from Ratings where(RollID = '$rollID' and FrameID = '$frameID')");
    $result = $this->getSingleRecord();
    $total = $result[0]; $count = $result[1];
    $score = ($count>0) ? $total/$count : 0;
    /* update the picture */
    $this->db->query("update Pictures set Rating = $score where (RollID = '$rollID' and FrameID = '$frameID')");
    }

  /* private: getAllRecords() returns an array of all records in the current resultset */
  function getAllRecords() {
    $arr = array();
    while ($this->db->next_record()) {
      $arr[] = $this->db->Record;
    }
    return $arr;
  }
  
  /* private: getSingleRecord() returns the single record in the current resultset
   * or FALSE if the resultset is empty */
  function getSingleRecord() {
    if ($this->db->next_record()) {
      return $this->db->Record;
    }
    return FALSE;
  }
  
  /* private: makeCols($arr) makes a comma delimited list of the keys of $arr */
  function makeCols($arr) {
    $keys = array();
    reset($arr);
    while (list($key, ) = each($arr)) {
      $keys[] = $key;
    }
    return (join(",", $keys));
  }

  /* private: makeVals($arr) makes a comma and single-quote delimited list
   * of the values of $arr, with special characters escaped */
  function makeVals($arr) {
    $values = array();
    reset($arr);
    while (list( ,$value) = each($arr)) {
      $values[] = "'" . addslashes($value) . "'";
    }
    return (join(",", $values));
  }

  /* private: makeUpdate($arr) makes a comma delimited list of the name/value
   * pairs of $arr, in the form name='value', with special characters escaped */
  function makeUpdate($arr) {
    $pairs = array();
    reset($arr);
    while (list($key, $value) = each($arr)) {
      $pairs[] =  $key . " = '" . addslashes($value) . "'";
    }
    return (join(",", $pairs));
  }

  /* public: checkDataVersion() makes sure our database/csv has the right structure,
   * and maybe fixes it if it doesn't */
  function checkDataVersion() {
  }

} /* end of class SloozeCtSql */
?>