<?php
/*

ALTER TABLE `tblForum` DROP FOREIGN KEY `tblForum.strLearner`;
ALTER TABLE `tblForum` ADD CONSTRAINT `tblForum.strLearner` FOREIGN KEY ( `refLearnerID` ) REFERENCES `tblLearner` ( `LearnerID` ) ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE `tblForum` DROP FOREIGN KEY `tblForum.strUser`;
ALTER TABLE `tblForum` ADD CONSTRAINT `tblForum.strUser` FOREIGN KEY ( `refUserID` ) REFERENCES `tblUser` ( `UserID` ) ON DELETE CASCADE ON UPDATE CASCADE // n/a

 *
 */

include_once("_nemo.list.cls.php");

class Forum extends NemoList
{
   private $ID = 0;

   public function __construct($DataKey)
   {

      parent::__construct($DataKey);

      $this->isSelectable = 
      $this->isPageable =
      $this->isSortable = 1;
   }

   public function getList()
   {
      global $SystemSettings;
      
      $dtLifespan = date("Y-m-d", strtotime($SystemSettings["ForumPostLifespan"]));
      
      $Where = "";

      $this->ListSQL("
         SELECT tblForum.ForumID, CASE WHEN tblLearner.LearnerID = -1 THEN sysUser.strUser ELSE tblLearner.strLearner END AS 'Learner/User'
            , tblForum.strTitle AS Title, Count(tblReply.ForumID) AS 'No Posts', tblForum.dtLastEdit AS 'First Posted'         
            , CASE WHEN Max(tblReply.dtPost) is NULL THEN tblForum.dtPost ELSE Max(tblReply.dtPost) END AS 'Latest Post'
         FROM sysUser RIGHT JOIN ((tblForum INNER JOIN tblLearner ON tblForum.refLearnerID = tblLearner.LearnerID) LEFT JOIN tblForum AS tblReply ON tblForum.ForumID = tblReply.refForumID) ON sysUser.UserID = tblForum.refUserID
         WHERE tblForum.refForumID = 0 $Where
         GROUP BY tblForum.ForumID, tblLearner.strLearner, tblForum.strTitle, tblForum.dtLastEdit
         HAVING LEFT(`Latest Post`,10) >= '$dtLifespan'
         ORDER BY Max(tblReply.dtLastEdit) DESC , tblForum.dtLastEdit DESC",0,"gen.forum.php");
      
      $this->Columns["Latest Post"]->html->align = "right";
      
      return $this->renderTable("Forum Post List");

   }

   public function getPosts($ForumID)
   {
      global $SystemSettings, $xdb, $BR;

      $Where = "";
      
      $this->isSelectable = 
      $this->isPageable =
      $this->isSortable = 0;

      $this->ListSQL("
         SELECT tblForum.dtPost AS 'Date', CASE WHEN tblLearner.LearnerID = -1 THEN sysUser.strUser ELSE tblLearner.strLearner END AS 'Learner/User'
            , REPLACE(tblForum.txtPost,CHAR(13),'$BR') AS Post, tblForum.blnActive as Active
            , concat('<input type=checkbox name=chkSelect[', ForumID,'] value=1 />') as 'Select'
         FROM sysUser RIGHT JOIN (tblForum INNER JOIN tblLearner ON tblForum.refLearnerID = tblLearner.LearnerID) ON sysUser.UserID = tblForum.refUserID
         WHERE refForumID= ". $xdb->qs($ForumID) ."
         ORDER BY tblForum.blnActive DESC, tblForum.dtPost DESC",0);
      
      $this->Columns["Select"]->html->align = "center";
      return $this->renderTable("Forum Post List");
      
      //, concat('<a href=\"?Action=Delete Post&ForumID=$ForumID&DeleteID=',ForumID,'\" >Delete</a>') as 'Delete'
   }

   public static function Save(&$ForumID)
   {
      //note: the UID prefic needs to be passed to the db.save(), else it will still gen a 6digit UID. UID = CourseTypeCode + Obf, e.g. "ABCNOT123456"
      $db = new NemoDatabase("tblForum", $ForumID, null, 0); //set Uninque restraint: none

      $db->SetValues($_POST);

      $db->Fields[strLastUser] = $_SESSION['USER']->USERNAME;

      $result = $db->Save(0,0);
//print_rr($result);
//print_rr($db);
      if($ForumID == "") $ForumID = $db->ID[ForumID];

      if($result->Error == 1)
         return $result->Message;
      else
         return "Details Saved. ";
   }
   
   public static function SavePost(&$ForumID)
   {
      global $DT;
      //note: the UID prefic needs to be passed to the db.save(), else it will still gen a 6digit UID. UID = CourseTypeCode + Obf, e.g. "ABCNOT123456"
      $db = new NemoDatabase("tblForum", 0, null, 0); //set Uninque restraint: none

      //$db->SetValues($_POST);
      $db->Fields[refLearnerID] = -1; // "system" ie not a learner
      $db->Fields[refUserID] = $_SESSION['USER']->ID;
      $db->Fields[refForumID] = $ForumID;
      $db->Fields[txtPost] = $_POST[txtReply];
      $db->Fields[dtPost] = $DT;
      $db->Fields[blnRead] = 1;
      $db->Fields[blnActive] = 1;
      $db->Fields[strLastUser] = $_SESSION['USER']->USERNAME;
//print_rr($db->Fields); die;
      $result = $db->Save(0,0);
//print_rr($result);
//print_rr($db);
      //if($ForumID == "") $ForumID = $db->ID[ForumID];

      if($result->Error == 1)
         return $result->Message;
      else
         return "Reply Saved. ";
   }

   public static function Delete($chkSelect)
   {
      global $xdb;
      //print_rr($chkSelect);
      if(count($chkSelect) > 0){
      foreach($chkSelect as $key => $value)
      {
         $xdb->doQuery("DELETE FROM tblForum WHERE ForumID = ". $xdb->qs($key) ." OR refForumID = ". $xdb->qs($key),0);         
      }
         return "Records Deleted.";
      }else{
         return "No Records Selected.";
      }
   }
   
   public static function AddPostForm()
   {
      global $BR;
      
      return "
         <table name=tblNewPost id=tblNewPost class='tblNemoList' cellpadding=0 cellspacing=1 width='100%'>
            <caption>Add a Post</caption>
            <tr>
               <td>
                  <textarea name=txtReply id=txtReply class='controlText controlWideMax' style='height: 100px; width: 100%;' ></textarea>$BR
                  $BR
                  <input class='btn fr controlButton' type='submit' name='Action' value='Reply' />
                  $BR
                  $BR
               </td>
            </tr>         
         </table>
         $BR";
   }

}
?>
