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

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.

// 20150220 - changes to RSVP for CPD points - ID number added to import cron and excel. Column for ID number added on CPD points lists - CL
*/

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

class RSVP extends NemoList
{
   private $ID = 0;
   public static $ActionAdd = "Add RSVP";
   public static $ActionEdit = "Edit RSVP";
   public static $ActionDelete = "Delete RSVP";
   public static $ActionEmail = "Email RSVP";
   public static $ActionExport = "Export RSVP";
   public static $ActionAttended = "Mark Attended";

   public function __construct($DataKey)
   {

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

   }

   public function getList($CourseID=0, $format="HTML")
   {
      global $SystemSettings, $SP, $BR, $xdb;
      $ActionAdd = self::$ActionAdd;
      $ActionEdit = self::$ActionEdit;
      $ActionDelete = self::$ActionDelete;
      $ActionEmail = self::$ActionEmail;
      $ActionExport = self::$ActionExport;
      $ActionAttended = self::$ActionAttended;

      if($CourseID != 0)
      {
         $Where = "WHERE refCourseID = ". $xdb->qs($CourseID);
      }
      $sql = "SELECT tblRSVP.RSVPID, tblRSVP.strDisplayName as 'Display Name', strID as 'ID', tblRSVP.strEmail as 'Email', tblRSVP.strStatus as 'Status'
               FROM tblRSVP $Where
               ORDER BY tblRSVP.strDisplayName ASC";
      $this->ListSQL($sql);
      $renderContent ="RSVP List
         ". ($format == "HTML" ?
               "<span style='float:right'>
               <input type='submit' name='Action' value='$ActionAdd' class=controlButton>$SP
               <input type='submit' name='Action' value='$ActionDelete' class=controlButton onClick=\"if(confirm('Are you sure you want to Delete the selected from RSVP List?')){return true;}else{return false;}\">$SP
               <input type='submit' name='Action' value='$ActionEmail' class=controlButton onClick=\"if(confirm('Are you sure you want to Email Invites to RSVP List?')){return true;}else{return false;}\">$SP
               <input type='submit' name='Action' value='$ActionExport' class=controlButton>$SP
               <input type='submit' name='Action' value='$ActionAttended' class=controlButton onClick=\"if(confirm('Are you sure you want to Mark the selected as attended?')){return true;}else{return false;}\">$SP
            </span>":"");
      return str_replace('chkSelect', 'chkSelectRSVP', str_replace('Edit', $ActionEdit, $this->renderTable($renderContent)));
   }

   public static function Save(&$RSVPID)
   {

      global $xdb;

      $rowCourse = $xdb->getRowSQL("SELECT * FROM tblCourse INNER JOIN tblCourseType ON refCourseTypeID = CourseTypeID WHERE CourseID =".$_POST['refCourseID'],0);
      $description = $_POST['strDisplayName'] . " [" . $_POST['strEmail'] . "]";

      $strVoucher = Voucher::GenerateVoucher($rowCourse->refCourseTypeID, $rowCourse->strCourseTypeCode, $description);

      $_POST['strVoucher'] = $strVoucher;
      //print_rr($_POST);die;

      //ini
      $db = new NemoDatabase("tblRSVP", $RSVPID, null, 0);

      //load
      $db->SetValues($_POST);
      $result = $db->Save(0,0);
      //post

      if($RSVPID == 0) $RSVPID = $db->ID[RSVPID];

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

// 20150220 - changes to RSVP for CPD points - ID number added to import cron and excel. Column for ID number added on CPD points lists - CL
   public static function Import($CourseID)
   {
      global $xdb;

      require_once ("Excel/excel_reader2.php");


      $rowCourse = $xdb->getRowSQL("SELECT * FROM tblCourse INNER JOIN tblCourseType ON refCourseTypeID = CourseTypeID WHERE CourseID =".$CourseID,0);
      $description = $_POST['strDisplayName'] . " [" . $_POST['strEmail'] . "]";

      $xls = new Spreadsheet_Excel_Reader($_FILES['RSVPImport']['tmp_name']);
      $data = $xls->sheets[0][cells];
      array_shift($data);

      foreach ($data as $key => $value) {
         $db = new NemoDatabase("tblRSVP", $RSVPID, null, 0);
         $strVoucher = Voucher::GenerateVoucher($rowCourse->refCourseTypeID, $rowCourse->strCourseTypeCode, $description);
         $db->Fields[refCourseID] = $CourseID;
         $db->Fields[strVoucher] = $strVoucher;
         $db->Fields[strDisplayName] = $value[1];
         $db->Fields[strEmail] = $value[2];
         $db->Fields[strID] = $value[3];
         $db->Fields[strStatus] = "New";
         $db->Fields[strLastUser] = $_SESSION['USER']->USERNAME;
         $db->Save(1,0);
      }

      return "RSVP Imported";

   }

   public static function MailRSVP($chkSelect, $CourseID)
   {
      global $xdb, $SystemSettings, $DT;

      //print_rr($SystemSettings);die;

      $refCourseTypeID = $xdb->getRowSQL("SELECT tblCourse.refCourseTypeID FROM tblCourse WHERE CourseID = ". $CourseID);
      $refCourseTypeID = $refCourseTypeID->refCourseTypeID;
      $refCourseTypeID;

      if($chkSelect > 0){
         $where = "AND RSVPID IN (". implode(",", array_keys($chkSelect)) .")";
      }

      $rst = $xdb->doQuery("SELECT tblRSVP.*, tblCourse.strCourse, tblCourse.refCourseTypeID, tblCourse.strCourseCode, tblCourse.dtStartDate, tblVenue.strVenue, tblVenue.txtAddress, tblCourse.strStartTime
               FROM tblRSVP INNER JOIN ((tblCourseVenue RIGHT JOIN tblCourse ON tblCourseVenue.refCourseID = tblCourse.CourseID) LEFT JOIN tblVenue ON tblCourseVenue.refVenueID = tblVenue.VenueID) ON tblRSVP.refCourseID = tblCourse.CourseID
               WHERE tblRSVP.refCourseID=$CourseID AND strStatus not IN ('Not Attending','Attending') $where",0);

      $OptOutURL = $SystemSettings[RSVPOptOut];
      $RegisterURL = $SystemSettings[RSVPRegister];

      if($_SERVER["HTTP_HOST"] == "lamp"){
         $OptOutURL = str_replace('www.knowmore.co.za', 'lamp/celestis_academy', $SystemSettings[RSVPOptOut]);
         $RegisterURL = str_replace('www.knowmore.co.za', 'lamp/celestis_academy', $SystemSettings[RSVPRegister]);
      }

      while($row = $xdb->fetch_object($rst))
      {
         $strCronFields[strEmail] = $row->strEmail;
         $strCronFields[DisplayName] = $row->strDisplayName;
         $strCronFields[refCourseTypeID] = $row->refCourseTypeID;
         $strCronFields[Course] = $row->strCourse;
         $strCronFields[StartDate] = $row->dtStartDate;
         $strCronFields[StartTime] = $row->strStartTime;
         $strCronFields[Venue] = $row->strVenue;
         $strCronFields[Address] = mynl2br($row->txtAddress);
         $strCronFields[OptOutURL] = $OptOutURL;
         $strCronFields[RegisterURL] = $RegisterURL;
         $strCronFields[UID] = $row->strVoucher;

         $strSerializedFields = serialize($strCronFields);
         $xdb->doQuery("
            INSERT INTO tblCronJob (strStatus, strJobType, strSerializedFields, strFirstUser, dtFirstEdit, strLastUser)
            VALUES ('ToDo', 'Workshop RSVP', '" . $strSerializedFields . "', 'rsvp::MailRSVP() + ".$_SESSION['USER']->USERNAME."', '$DT', 'Cron Daily.GenerateInvite')",0);
      }

      return "RSVP email(s) have been scheduled. <i class='textGraphite'>[Please note that the emails will be sent next time the scheduler runs once every 15 minutes]</i> ";

   }

   public static function ChangeStatus($chkSelect, $strStatus)
   {
      global $xdb;

      foreach($chkSelect as $key => $value)
      {
         $xdb->doQuery("UPDATE tblRSVP SET strStatus='$strStatus' WHERE RSVPID=$key");

         //$row = $xdb->getRowSQL("SELECT tblRSVP.refCourseID, tblLearner.LearnerID, tblRSVP.RSVPID
         //               FROM tblLearner INNER JOIN tblRSVP ON tblLearner.strEmail = tblRSVP.strEmail
         //               WHERE (((tblRSVP.RSVPID)=$key))
         //               ",0);

         $row = $xdb->getRowSQL("SELECT tblRSVP.refCourseID, tblLearner.LearnerID, tblRSVP.RSVPID, tblRSVP.strDisplayName as 'strRSVPName', tblLearner.strDisplayName as 'strLearnerName', tblRSVP.strID, tblRSVP.strEmail
               FROM tblLearner RIGHT JOIN tblRSVP ON tblLearner.strEmail = tblRSVP.strEmail
               WHERE (((tblRSVP.RSVPID)=$key))
               ",0);

         if($row)
         {
            if( $strStatus == "Attended")
            {
               $strDisplayName = $row->strLearnerName;
               if($row->LearnerID =="")
               {
                  $strDisplayName = $row->strRSVPName;
                  $row->LearnerID = -2;
               }
               $blnPoints = grantCPDPoints($row->LearnerID, $row->refCourseID, $strDisplayName, $row->strID, $row->strEmail, "CPD Points Workshop");
            }

         }
      }

      return "Status Changed.";
   }

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

   public static function MailRSVPConfirmation($arrCourseID)
   {
      global $xdb, $SystemSettings, $DT;
      echo "disabled"; die;
      $CourseID = 342;

      //if not an array, force it to an array. i.e only one course
      //if(!is_array($arrCourseID))
      //{
       //  $arrCourseID[342] = 342;
      //}
      //print_rr($arrCourseID);
      //foreach($arrCourseID as $CourseID)
      //{
         //print_rr($SystemSettings);die;

         // $refCourseTypeID = $xdb->getRowSQL("SELECT tblCourse.refCourseTypeID FROM tblCourse WHERE CourseID = ". $CourseID);
         // $refCourseTypeID = $refCourseTypeID->refCourseTypeID;
         // $refCourseTypeID;

         $rowCourse = $xdb->getRowSQL("SELECT * FROM tblCourse WHERE CourseID = '$CourseID'");
         $strCourse = "$rowCourse->strCourse";// [$rowCourse->strCourseCode]";
         $strStartDate = date("j F Y", strtotime($rowCourse->dtStartDate));
         $strStartTime = $rowCourse->strStartTime;
         $refCourseTypeID = $rowCourse->refCourseTypeID; //20130802 - used in getCourseTypeEmailTemplate() - pj

         //20151015 - get venue details
         $rowVenue = $xdb->getRowSQL("SELECT tblVenue.strVenue, tblVenue.txtAddress FROM tblCourse INNER JOIN tblCourseVenue ON tblCourseVenue.refCourseID=tblCourse.CourseID INNER JOIN tblVenue ON tblVenue.VenueID = tblCourseVenue.refVenueID WHERE CourseID = '$CourseID'");
         $txtVenue = $rowVenue->txtAddress; //$rowVenue->strVenue .", ".

         // $rst = $xdb->doQuery("SELECT * FROM tblRSVP WHERE refCourseID = '$CourseID' AND strStatus='Attending'");
         $rst = $xdb->doQuery("SELECT tblCourse.CourseID, tblCourseLearner.LearnerID, tblLearner.strDisplayName, tblLearner.strEmail, tblLearner.strPassword
         FROM (tblRegion INNER JOIN ((SELECT tblLearner.LearnerID, tblLicence.refCourseID as CourseID, Min(tblLicence.dtDateIssued) as dtDateIssued
            FROM tblLicence INNER JOIN tblLearner ON tblLicence.refLearnerID = tblLearner.LearnerID
            WHERE tblLicence.strStatus='Active' AND refCourseID = $CourseID and tblLearner.LearnerID IN ()
            GROUP BY tblLearner.LearnerID, tblLicence.refCourseID
         )  AS tblCourseLearner INNER JOIN tblLearner ON tblCourseLearner.LearnerID = tblLearner.LearnerID) ON tblRegion.RegionID = tblLearner.refRegionID) INNER JOIN tblCourse ON tblCourseLearner.CourseID = tblCourse.CourseID
         ORDER BY tblLearner.strLearner");

         while($rowRSVP = $xdb->fetch_object($rst))
         {
            // $rowLearner = $xdb->getRowSQL("SELECT * FROM tblLearner WHERE strEmail = '$rowRSVP->strEmail'");

            $nemoEmail = new NemoEmail($rowRSVP->strEmail, "" , 0);

            //$nemoEmail->SentFromDatabase(10,"exiledbandit@gmail.com"); die;

            //20130802 - send course specific reg email, default to "Register" ETemplate if none is found.
            $nemoEmail->LoadEmailTemplate(getCourseTypeEmailTemplate("Confirmation", "Confirmation", $refCourseTypeID));

            //images are located in celestis/images not webadmin/images
            $strBaseURL = str_replace("/webadmin", "", $SystemSettings[BASE_URL]);

            $arrValues[DisplayName] = $rowRSVP->strDisplayName;
            $arrValues[Email] = $rowRSVP->strEmail;
            $arrValues[Password] = $rowRSVP->strPassword;
            $arrValues[Course] = $strCourse;
            $arrValues[Venue] = $txtVenue;
            $arrValues[StartDate] = $strStartDate;
            $arrValues[StartTime] = $strStartTime;
            //20150713 -Moved substituted custom Logo's outside of nemo.email class - Christiaan
            $arrValues[LogoPFI] = "<a href='".$strBaseURL."index.php"."'><img src='".$strBaseURL.$SystemSettings[EmailLogoPFI]."' ></a>";
            $arrValues[InviteHeader] = "<img src='".$strBaseURL.$SystemSettings[EmailLogoInvite]."' >";

            $nemoEmail->Substitute($arrValues);

            $nemoEmail->addHeader("FROM", $SystemSettings["SMTP Send As"]);
            //$nemoEmail->addHeader("BCC", $SystemSettings["SMTP BCC"]);
            //$nemoEmail->Bcc = $SystemSettings["SMTP BCC"];
            $nemoEmail->from = $SystemSettings["SMTP Send As"];
            //print_rr($nemoEmail); die;
            $nemoEmail->Send();
         }
      //}
   }
}
