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" ?
"
$SP
$SP
$SP
$SP
$SP
":"");
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. [Please note that the emails will be sent next time the scheduler runs once every 15 minutes] ";
}
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] = "![](".$strBaseURL.$SystemSettings[EmailLogoPFI].")
";
$arrValues[InviteHeader] = "![](".$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();
}
//}
}
}