<?php
/*
ALTER TABLE `tblEmail` DROP FOREIGN KEY `tblEmail.strEmailTemplate`;

NB! SET FOREIGN_KEY_CHECKS=0; refETID is not always set
ALTER TABLE `tblEmail` ADD CONSTRAINT `tblEmail.strEmailTemplate` FOREIGN KEY ( `refEmailTemplateID` ) REFERENCES `tblEmailTemplate` ( `EmailTemplateID` ) ON DELETE CASCADE ON UPDATE CASCADE ;

*BR: only Security.Special to send New Emails.
*/

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

class RSVP extends NemoList
{
   private $ID = 0;

   public function __construct($DataKey)
   {
      //filters
      $this->Filters[frSearch]->tag = "input";
      $this->Filters[frSearch]->html->value = "";
      $this->Filters[frSearch]->html->class = "controlText";

      $this->Filters[frStatus]->tag = "select";
      $this->Filters[frStatus]->html->value = "";
      $this->Filters[frStatus]->html->class = "controlText";
      $this->Filters[frStatus]->sql = "SELECT '' AS ControlValue, '- All -' AS ControlText
                        UNION ALL
                        SELECT strStatus AS ControlValue, strStatus AS ControlText
                        FROM tblEmail
                        GROUP BY strStatus
                        ORDER BY ControlText ASC";


      //construct
      parent::__construct($DataKey);

   }

   public function getList()
   {
      global $SystemSettings, $DT, $xdb;

      $Where = "";

      if($this->Filters[frSearch]->html->value != "")
      {
         $like = "LIKE (". $xdb->qs("%". $this->Filters[frSearch]->html->value ."%") .")";
         $Where .= " AND (xtblInvite.strDisplayName $like
                        OR xtblInvite.txtNotes $like
                        OR tblEmail.strTo $like
                        OR tblLearner.strLearner $like) ";
      }

      if($this->Filters[frStatus]->html->value != "")
      {
         $Where .= " AND tblEmail.strStatus = '" . $this->Filters[frStatus]->html->value . "'";
      }
      //NEW QUERY
      $this->listSQL("  
                        SELECT 
                           concat(xtblInvite.strDisplayName, ' (',xtblInvite.txtNotes,')') AS Invitee, tblEmail.strTo AS Email, tblEmail.strStatus AS 'Email Status', tblEmail.dtEmail as 'Date Time', concat('<a href=\"learner.php?Action=Edit&LearnerID=',LearnerID,'\" target=_blank >',tblLearner.strLearner,'</a>') AS Learner,
                           tblCourse.strCourse AS Course, tblCourse.dtStartDate AS 'Start Date', tblRegion.strRegion AS 'Registered Region'                        
                        FROM tblRegion 
                        RIGHT JOIN ((tblLicence 
                           RIGHT JOIN (xtblInvite 
                              INNER JOIN (tblLearner 
                                 RIGHT JOIN tblEmail ON tblLearner.strEmail = tblEmail.strTo) ON xtblInvite.strEmail = tblEmail.strTo) ON tblLicence.refLearnerID = tblLearner.LearnerID) 
                        LEFT JOIN tblCourse ON tblLicence.refCourseID = tblCourse.CourseID) ON tblRegion.RegionID = tblLearner.refRegionID
                        WHERE tblEmail.refEmailTemplateID=28 AND xtblInvite.blnProcessed > 0 AND xtblInvite.ID > 0 $Where

                        GROUP BY xtblInvite.strDisplayName, tblEmail.strTo, tblEmail.refEmailTemplateID, tblLearner.strLearner, tblCourse.strCourse, tblCourse.dtStartDate, tblRegion.strRegion
                        ORDER BY xtblInvite.strDisplayName
                     ", 0);





      return $this->renderTable("IFA/PFA RSVP List Aug-Sep 2013");
   }

   public static function Save(&$EmailID)
   {
      global $DT, $SystemSettings;

      $db = new NemoDatabase("tblEmail", $EmailID, null, 0);


      //$db->SetValues($_POST);
      //ONLY save txtNotes

      $db->Fields[txtNotes] = $_POST[txtNotes];

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

      $result = $db->Save();
      //print_rr($result);


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

   public static function Delete($chkSelect)
   {
      global $xdb;
      //print_rr($chkSelect);
      if(count($chkSelect) > 0){
      foreach($chkSelect as $key => $value)
      {
         $xdb->doQuery("DELETE FROM tblEmail WHERE EmailID = ". $xdb->qs($key));
      }
         return "Records Deleted. ";
      }
   }


}
?>
