Source for file db_messages.php

<?php
/**
 * <b>Database Class : Messages</b><br />
 * Provides all database access methods for the audio messages section
 * @author Kristen O'Brien <kristen_paristemi-com>
 * @copyright Copyright 2004, Kristen O'Brien
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 * @link http://www.paristemi.com Paristemi Main Site
 * @package paristemi
 * @subpackage paristemi_database_layer
 * @since Build 0.5
 * @version Build 0.7
 * @filesource
 */
/**
 * <b>Database Class : Messages</b><br />
 * Provides all database access methods for the audio messages section
 * @author Kristen O'Brien <kristen_paristemi-com>
 * @copyright Copyright 2004, Kristen O'Brien
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 * @link http://www.paristemi.com Paristemi Main Site
 * @package paristemi
 * @subpackage paristemi_database_layer
 * @since Build 0.5
 * @version Build 0.7
 * @filesource
 */
class DB_Messages {    
    
    var $grouping = array();
    var $bible_list = array();
    
    function DB_Messages() { }
    
    function setGrouping($group) { $this->grouping = $group; }
    function setBibleList($blist) { $this->bible_list = $blist; }
    
    function &GetBooksUsed() {
       $books = Array();
       $ordered_books = Array();
       $result = mysql_query("SELECT DISTINCT BookReference FROM Audio 
                              WHERE Category = 'Audio Message' AND BookReference != 'No Reference'");
       
       while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
          $books[] = $row['BookReference'];
       }
       
       $found_book = false;
       foreach($this->bible_list as $bible_book) {
          foreach($books as $book) {
             if($book == $bible_book && !$found_book) {
                $ordered_books[] = $book;
                $found_book = true;
             }
          }
          $found_book = false;
       }
       
       return $ordered_books;
    }
    
    function &GetTopicsUsed() {
       $topics = Array();
       $result = mysql_query("SELECT DISTINCT Topic FROM Audio 
                              WHERE Category = 'Audio Message' AND Topic != 'No Topic'");
              
       while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
          $tok = strtok($row['Topic'],",");
          while ($tok) {
             $topics[] = trim($tok);
             $tok = strtok(",");
          }
       }
       
       $unique_topics = array_values(array_unique($topics));
       sort($unique_topics);
       return $unique_topics;
    }
    
    function &GroupFunct($item) {
        $flag = 0;
        foreach($this->grouping as $letter) {
            if($item{0} == $letter) { $flag = 1; }
        }
        return $flag;
    }
    
    function &GetTopicsUsedByGroup($group) {
       $this->grouping = $group;
       $topics = Array();
       $result = mysql_query("SELECT DISTINCT Topic FROM Audio 
                              WHERE Category = 'Audio Message' AND Topic != 'No Topic'");
              
       while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
          $tok = strtok($row['Topic'],",");
          while ($tok) {
             $topics[] = trim($tok);
             $tok = strtok(",");
          }
       }
       
       $unique_topics = array_values(array_unique($topics));
       $new_arr = array_filter($unique_topics,array($this,"GroupFunct"));
       sort($new_arr);
       return $new_arr;
    }
    
    function &GetMonthsUsedByYear($year) {
        $result = mysql_query("select DISTINCT EXTRACT(YEAR_MONTH FROM I.Start) as yearmon, 
                                      YEAR(I.Start) as startyear, MONTHNAME(I.Start) as startmon
                               from Instance I, Audio A
                               where I.AudioID = A.ID and I.AudioID != 1 and A.Category = 'Audio Message'
                                     and YEAR(I.Start) = $year
                               order by yearmon asc");
        $data = array();
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $data[] = $row['startmon']." ".$row['startyear'];
        }
        return $data;
    }
        
    function &GetYearsUsed() {
        $result = mysql_query("select DISTINCT YEAR(I.Start) as startyear
                               from Instance I, Audio A
                               where I.AudioID = A.ID and I.AudioID != 1 and A.Category = 'Audio Message'
                               order by startyear desc");
        $data = array();
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $data[] = $row['startyear'];
        }
        return $data;
    }
    
    function &GetMonthsUsed() {
        $result = mysql_query("select DISTINCT EXTRACT(YEAR_MONTH FROM I.Start) as yearmon, 
                                      YEAR(I.Start) as startyear, MONTHNAME(I.Start) as startmon
                               from Instance I, Audio A
                               where I.AudioID = A.ID and I.AudioID != 1 and A.Category = 'Audio Message'
                               order by yearmon desc");
        $data = array();
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $data[] = $row['startyear']." ".$row['startmon'];
        }
        return $data;
    }
    
    //Pass it the ID.
    //There must be an ID associated with all of the elements.
    
    function &GetAudioData(&$id) {
        $result = mysql_query("select I.ID as IID, I.Title as ITitle, I.Start as IStart, I.Stop as IStop,
                                      C.ID as CID, C.First as CFirst, C.Last as CLast,
                                      C.Title as CTitle, C.ShowMainTitle, C.ShowMainFirstName,C.ShowMainLastName,
                                      C.ShowEmail as CShowEmail, L.ID AS LID, L.Name AS LName,
                                      A.ID, A.Title, A.LinkStream, A.Description, A.BookReference, 
                                      A.StartVerse, A.StopVerse, A.Topic, A.LinkDownload,
                                      A.PPTLink, A.PDFLink
                               from Instance I, Contact C, Location L, Audio A
                               where A.ID = $id and I.AudioID = $id
                                      and C.ID = I.ContactID and L.ID = I.LocationID
                                      and A.Category = 'Audio Message'
                               order by I.Start
                               limit 1");
 
               $row = mysql_fetch_array($result, MYSQL_ASSOC);
 
           $startstamp = strtotime($row['IStart']);
           $stopstamp = strtotime($row['IStop']);
           $start = getDate($startstamp);
           $stop = getDate($stopstamp);
 
           $fullname = "";
           if($row['ShowMainTitle']) { $fullname = $row['CTitle']." "; }
           if($row['ShowMainFirstName']) { $fullname .= $row['CFirst']." "; }
           if($row['ShowMainLastName']) { $fullname .= $row['CLast']; }
           $data = new AudioDataDetailed($row['IID'], $row['ITitle'],
                           $start, $stop, $row['CID'], $row['CShowEmail'], $fullname,
                           $row['ID'], $row['Title'], $row['LinkStream'], $row['LinkDownload'],
                           $row['BookReference'],$row['StartVerse'],$row['StopVerse'],
                           $row['Topic'],$row['LID'], $row['LName'], $row['Description'],
                           $row['PPTLink'],$row['PDFLink']);
        
        return $data;
    }
    
    //There must be an ID associated with all of the elements.
    
    function &GetAllMessages($order="A.ID") {
        $data = Array();
        $result = mysql_query("select I.ID as IID, I.Start as IStart, I.Stop as IStop, 
                                      A.ID, A.Title, A.LinkStream, A.LinkDownload,A.Description,
                                      A.BookReference, A.StartVerse, A.StopVerse, A.Topic,
                                      A.PPTLink, A.PDFLink
                  from Instance I, Audio A
                  where I.AudioID = A.ID and I.AudioID != 1 and A.Category = 'Audio Message'
                  order by $order desc");
 
        //Process each row.
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
 
           $startstamp = strtotime($row['IStart']);
           $stopstamp = strtotime($row['IStop']);
           $start = getDate($startstamp);
           $stop = getDate($stopstamp);
 
           $data[] = new MessageData($row['IID'], $row['ID'], $start, $stop, $row['Title'], $row['Description'], 
                                     $row['LinkStream'],$row['LinkDownload'],
                                     $row['BookReference'],$row['StartVerse'], $row['StopVerse'], $row['Topic'],
                                     $row['PPTLink'],$row['PDFLink']);
        }
 
        return $data;
    }
    
        //There must be an ID associated with all of the elements.
    
    function &GetAllMessagesByLimit($limit,$blimit) {
        $data = Array();
        $result = mysql_query("select I.ID as IID, I.Start as IStart, I.Stop as IStop, 
                                      A.ID, A.Title, A.LinkStream, A.LinkDownload,A.Description,
                                      A.BookReference, A.StartVerse, A.StopVerse, A.Topic,
                                      A.PPTLink, A.PDFLink
                  from Instance I, Audio A
                  where I.AudioID = A.ID and I.AudioID != 1 and A.Category = 'Audio Message'
                  order by A.Title asc
                  limit $blimit,$limit");
 
        //Process each row.
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
 
           $startstamp = strtotime($row['IStart']);
           $stopstamp = strtotime($row['IStop']);
           $start = getDate($startstamp);
           $stop = getDate($stopstamp);
 
           $data[] = new MessageData($row['IID'], $row['ID'], $start, $stop, $row['Title'],
                                     $row['Description'],$row['LinkStream'],$row['LinkDownload'],
                                     $row['BookReference'],$row['StartVerse'], $row['StopVerse'], $row['Topic'],
                                     $row['PPTLink'],$row['PDFLink']);
        }
 
        return $data;
    }
    
       //There must be an ID associated with all of the elements.
    
    function &SearchMessagesType(&$item,$atype,$limit,$blimit) {
        $data = Array();
 
        $query_string = "select DISTINCTROW I.ID as IID, A.ID, A.Title, 
                               A.Description, A.LinkStream, A.LinkDownload, A.Category, A.Topic,
                               A.BookReference, A.StartVerse, A.StopVerse,
                               A.PPTLink, A.PDFLink
                               from Instance I, Audio A
                               where I.AudioID = A.ID AND
                               I.AudioID != 1 AND (";
 
       foreach($atype as $type) {           
        if($type == "topic") {
           $query_string = $query_string . "A.Topic LIKE '%$item%' OR ";
        }
        else if($type == "description") {
           $query_string = $query_string . "A.Description LIKE '%$item%' OR ";
        }
        else if($type == "title") {
           $query_string = $query_string . "A.Title LIKE '%$item%' OR ";
        }
        else { die("Invalid Parameters"); }
       }
       if(count($atype) == 0) { die("Invalid Parameters"); }
       
        $query_string = substr($query_string,0,strlen($query_string)-4);
        $query_string = $query_string . ") AND A.Category = 'Audio Message'
                                          order by A.Title
                                          limit $blimit,$limit";
        $result = mysql_query($query_string);
 
        //Process each row.
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
            $data[] = new MessageData($row['IID'], $row['ID'], "", "", $row['Title'],
                                      $row['Description'],$row['LinkStream'], $row['LinkDownload'],
                                      $row['BookReference'],$row['StartVerse'], $row['StopVerse'], $row['Topic'],
                                            $row['PPTLink'],$row['PDFLink']);
        }
 
        return $data;
    }
    
           //There must be an ID associated with all of the elements.
    
    function &SearchMessagesCount(&$item,$atype) {
        $query_string = "SELECT DISTINCTROW COUNT(*) 
                               from Audio A 
                               where (";
       foreach($atype as $type) {
        if($type == "topic") {
           $query_string = $query_string . "A.Topic LIKE '%$item%' OR ";
        }
        else if($type == "description") {
           $query_string = $query_string . "A.Description LIKE '%$item%' OR ";
        }
        else if($type == "title") {
           $query_string = $query_string . "A.Title LIKE '%$item%' OR ";
        }
        else { $this->Error("Invalid Parameters"); }
       }
        
        $query_string = substr($query_string,0,strlen($query_string)-4);
        $query_string = $query_string . ") AND A.Category = 'Audio Message'";
        $result = mysql_query($query_string);
        $row = mysql_fetch_array($result, MYSQL_ASSOC);
        foreach($row as $item) { $count = $item; }
        return $count;
    }
 
    
        //There must be an ID associated with all of the elements.
    
    function &GetAllAudio() {
        $data = Array();
        $result = mysql_query("select A.ID, A.Title, A.LinkStream, A.LinkDownload,
                                       A.BookReference, A.StartVerse, A.StopVerse, A.Topic,
                                       A.PPTLink, A.PDFLink
                  from Audio A
                  order by A.ID desc");
 
        //Process each row.
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
           $data[] = new MessageData("", $row['ID'], "", "", $row['Title'],
                                     $row['Description'],$row['LinkStream'], $row['LinkDownload'],
                                     $row['BookReference'],$row['StartVerse'], $row['StopVerse'], $row['Topic'],
                                           $row['PPTLink'],$row['PDFLink']);
        }
 
        return $data;
    }
    
    //There must be an ID associated with all of the elements.
    
    function &GetMessagesRange($first,$last) {
        $data = Array();
        $result = mysql_query("select I.ID as IID, I.Start as IStart, I.Stop as IStop, A.Description,
                                      A.ID, A.Title, A.LinkStream, A.LinkDownload, A.PPTLink, A.PDFLink, 
                                      A.BookReference, A.StartVerse, A.StopVerse, A.Topic
                  from Instance I, Audio A
                  where I.AudioID = A.ID and I.AudioID != 1 and A.Category = 'Audio Message'
                  order by A.ID desc
                  limit $first,$last");
 
        //Process each row.
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
 
           $startstamp = strtotime($row['IStart']);
           $stopstamp = strtotime($row['IStop']);
           $start = getDate($startstamp);
           $stop = getDate($stopstamp);
 
           $data[] = new MessageData($row['IID'], $row['ID'], $start, $stop, $row['Title'],
                                     $row['Description'],$row['LinkStream'], $row['LinkDownload'],
                                     $row['BookReference'],$row['StartVerse'], $row['StopVerse'], $row['Topic'],
                                           $row['PPTLink'],$row['PDFLink']);
        }
 
        return $data;
    }
    
    //Pass it the Item & Type.
    //There must be an ID associated with all of the elements.
    
    function &GetAudioDataType(&$item, $type) {
        $item = addslashes($item);
        $data = Array();
        $query_string = "select I.ID as IID, I.Title as ITitle, I.Start as IStart, I.Stop as IStop,
                               C.ID as CID, C.First as CFirst, C.Last as CLast, C.Title as CTitle,
                               C.ShowMainTitle, C.ShowMainFirstName, C.ShowMainLastName,
                               C.ShowEmail as CShowEmail, L.ID AS LID, L.Name AS LName, 
                               A.ID, A.Title, A.LinkStream as ALinkStream, A.Description as ADesc,
                               A.BookReference, A.StartVerse, A.StopVerse, A.Topic, A.LinkDownload,
                               A.PPTLink, A.PDFLink
                      from Instance I, Series S, Contact C, Location L, Audio A ";
                      
        if($type == "book") {
           $query_string = $query_string . "where A.BookReference LIKE '%$item%' ";
        }
        else if($type == "topic") {
           $query_string = $query_string . "where A.Topic LIKE '%$item%' ";
        }
        else if($type == "date") {
            $dateinfo = explode(" ",$item); $year = $dateinfo[0]; $mon = $dateinfo[1];
            $query_string = $query_string . "where YEAR(I.Start) = $year AND MONTHNAME(I.Start) = '$mon' ";
        }
        else { $this->Error('Invalid Parameters'); }
        
        $query_string = $query_string . "and S.ID = I.SeriesID and C.ID = I.ContactID and 
                                          L.ID = I.LocationID and A.ID = I.AudioID
                                          and A.Category = 'Audio Message'
                                          and A.Title != 'No Audio Message'
                                          order by I.Start desc";
        
        $result = mysql_query($query_string);
                      
 
        //Process each row.
        while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
 
           $startstamp = strtotime($row['IStart']);
           $stopstamp = strtotime($row['IStop']);
           $start = getDate($startstamp);
           $stop = getDate($stopstamp);
 
           $fullname = "";
           if($row['ShowMainTitle']) { $fullname = $row['CTitle']." "; }
           if($row['ShowMainFirstName']) { $fullname .= $row['CFirst']." "; }
           if($row['ShowMainLastName']) { $fullname .= $row['CLast']; }
           $data[] = new AudioDataDetailed($row['IID'], $row['ITitle'],
                           $start, $stop, $row['CID'], $row['CShowEmail'], $fullname,
                           $row['ID'], $row['Title'], $row['ALinkStream'], $row['LinkDownload'],
                           $row['BookReference'], $row['StartVerse'], $row['StopVerse'],
                           $row['Topic'],$row['LID'], $row['LName'], $row['ADesc'],
                                           $row['PPTLink'],$row['PDFLink']);
        }
 
        return $data;
    }
}
 
 
/**
 * <b>Database Container Class : Audio Detailed</b><br />
 * Contains all of the specific data for an audio message.
 * @author Kristen O'Brien <kristen_paristemi-com>
 * @copyright Copyright 2004, Kristen O'Brien
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 * @link http://www.paristemi.com Paristemi Main Site
 * @package paristemi
 * @subpackage paristemi_database_layer
 * @since Build 0.5
 * @version Build 0.7
 * @filesource
 */
    class AudioDataDetailed {
    var $IID;
    var $ITitle;
    var $Start;
    var $Stop;
    var $CID;
    var $CShowEmail;
    var $CName;
    var $ID;
    var $Title;
    var $ALinkStream;
    var $ALinkDownload;
    var $PPTLink;
    var $PDFLink;
    var $BookReference;
    var $StartVerse;
    var $StopVerse;
    var $Topic;
    var $LID;
    var $LName;
    var $Description;
    
    function AudioDataDetailed($iid, $ititle, $start, $stop, $cid, $cshowemail, $cname,
                         $id, $title, $link, $linkdl, $_bookreference, $_startverse,
                         $_stopverse, $_topic, $lid, $lname, $desc,$pptlink,$pdflink) {
        $this->IID = $iid;
        $this->ITitle = $ititle;
        $this->Start = $start;
        $this->Stop = $stop;
        $this->CID = $cid;
        $this->CShowEmail = $cshowemail;
        $this->CName = $cname;
        $this->ID = $id;
        $this->Title = $title;
        $this->ALinkStream = $link;
        $this->ALinkDownload = $linkdl;
        $this->BookReference = $_bookreference;
        $this->StartVerse = $_startverse;
        $this->StopVerse = $_stopverse;
        $this->Topic = $_topic;
        $this->LID = $lid;
        $this->LName = $lname;
        $this->Description = $desc;
          $this->PPTLink = $pptlink;
          $this->PDFLink = $pdflink;
    }
}
 
/**
 * <b>Database Container Class : Message</b><br />
 * Contains all of the important data for an audio message.
 * @author Kristen O'Brien <kristen_paristemi-com>
 * @copyright Copyright 2004, Kristen O'Brien
 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
 * @link http://www.paristemi.com Paristemi Main Site
 * @package paristemi
 * @subpackage paristemi_database_layer
 * @since Build 0.5
 * @version Build 0.7
 * @filesource
 */
class MessageData {
   var $IID;
   var $ID;
   var $Start;
   var $Stop;
   var $Title;
   var $Description;
   var $ALinkStream;
   var $ALinkDownload;
   var $PPTLink;
   var $PDFLink;
   var $BookReference;
   var $StartVerse;
   var $StopVerse;
   var $Topic;
   
   function MessageData($iid, $id, $start, $stop, $title, $desc, $alink, $alinkdl, $bookreference,
                        $startverse, $stopverse, $topic,$pptlink,$pdflink) {
      $this->IID = $iid;
      $this->ID = $id;
      $this->Start = $start;
      $this->Stop = $stop;
      $this->Title = $title;
      $this->Description = $desc;
      $this->ALinkStream = $alink;
      $this->ALinkDownload = $alinkdl;
      $this->BookReference = $bookreference;
      $this->StartVerse = $startverse;
      $this->StopVerse = $stopverse;
      $this->Topic = $topic;
      $this->PPTLink = $pptlink;
      $this->PDFLink = $pdflink;
   }
}
 
?>