<?php
$username = "root";
$password = "";
$hostname = "localhost";
//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
$selected = mysql_select_db("calendarix",$dbhandle)
or die("Could not select database");
echo "\nLoop2 read";
for($i=0;$i<10;$i++)
{
//stop the loop hogging processor time
sleep(2);
reademail();
}
function reademail()
{
$user = "";
$pass = "";
$mbox = imap_open("{imap.gmail.com:993/imap/ssl}INBOX",$user,$pass) or die("can't connect: " . imap_last_error());
$mc = imap_check($mbox);
$messages = imap_fetch_overview($mbox,"1:{$mc->Nmsgs}",0);
foreach ($messages as $message) {
//check to see if message has been viewed yet
$seen_msg = $message->seen;
//retrieve message number, date, from, subject, uid, seen,
//and message body from message
if (!$seen_msg){
$msgbody = imap_fetchbody($mbox,$message->msgno,1);
if(strpos($msgbody, "EventID='") !== false)
{
$right = right($msgbody, strlen($msgbody) - (strpos($msgbody, "EventID='") + 9));
$eventid = left($right, strpos($right, "'"));
$msgfrom = $message->from;
$subject = $message->subject;
$right = right($msgfrom, strlen($msgfrom) - (strpos($msgfrom, "<") + 1));
$from = left($right, strpos($right, ">"));
if(is_numeric($eventid))
{
$tgl1 = strtotime($message->date); //convert to timestamp
$tgl2 = date("d/m/Y H:i",$tgl1); //format date from timestamp
echo "\n\n".$tgl2;
echo "\n".$subject;
//firstline defined as first '\n'
$firstline = left($msgbody, strpos($msgbody, "\n"));
echo "\nEventID:".$eventid;
echo "\nFirstline:".$firstline;
echo "\nFrom:".$from;
if(strpos(strtoupper($firstline), "YES") !== false)
{
echo " will be attending event";
updatevoteemail($from, $eventid, 1, $firstline);
}
else if(strpos(strtoupper($firstline), "NO") !== false)
{
echo " will not be attending event";
updatevoteemail($from, $eventid, 0, $firstline);
}
else
{
echo " has not deided";
}
}
}
$from = $message->from;
echo "\nFrom:".$from;
//sms reply processing
if(strpos(strtoupper($from), "MESSAGENET") !== false)
{
echo "\nMessagenet message:";
//get phone number from email address, format is hide@address.com - this is split to just number then number split to just 04 number from 614 number
$smsemail = left($from, strpos(strtoupper($from), "@MESSAGENET"));
if(strpos($smsemail, "61") == 0)
{
$right = right($smsemail, strlen($smsemail) - (strpos($smsemail, "614") + 3));
$smsemail = "04".$right;
}
echo "\nNumber:".$smsemail;
//messagenet.com.au also send back notifications that an sms has been sent - this filters notifications from replies as only replies will be a numeric emailaddress
if(is_numeric($smsemail))
{
$eventid = geteventid($msgbody);
if(isset($eventid) && $eventid <> '')
{
//if the word 'yes' exists, this takes priority as no can also be used for other words such as 'not'
//if the word yes does not exist but no does, it is cast as a no vote.
if(strpos(strtoupper($msgbody), "YES") !== false)
{
echo " will be attending event ".$eventid;
updatevotesms($smsemail, $eventid, 1, $msgbody);
}
else if(strpos(strtoupper($msgbody), "NO") !== false)
{
echo " will not be attending event ".$eventid;
updatevotesms($smsemail, $eventid, 0, $msgbody);
}
else
{
echo " has not decided";
}
}
else
echo " has not entered an eventid";
}
}
// this logs all incoming emails to the database
recordemail($from, $message->subject, $msgbody);
//deletes the email from the messagebox to prevent re-reading the same email
imap_delete($mbox, $message->msgno);
} // end if
} // end foreach
imap_expunge($mbox);
}
//this method pulls out the number from the text by removing everything except numbers from the string
function geteventid($msgbody)
{
$eventid = ereg_replace("[^0-9]", "", $msgbody);
return $eventid;
}
function recordemail($from, $subject, $message)
{
//POSIX regular expressions used for text data type in database
$msgstring = ereg_replace("[^A-Za-z0-9[:space:]]", "", $message);
$subjstring = ereg_replace("[^A-Za-z0-9[:space:]]", "", $subject);
echo $msgstring;
$query = "INSERT INTO emaillog (Subject, Email, message) VALUES ('".$subjstring."','".$from."','".$msgstring."')";
mysql_query($query) or die('Error, insert query failed');
}
function updatevotesms($phone, $eventid, $decision, $firstline)
{
//get each userid for the one email address for the one event(there may be more than one userid for one address)
$query = "SELECT user_id
FROM (
calendar_users
INNER JOIN groupusers ON calendar_users.user_id = groupusers.userid
)
INNER JOIN calendar_events ON groupusers.groupid = calendar_events.cat
WHERE calendar_users.phone = '".$phone."'
AND id =".$eventid;
$result = mysql_query($query);
$rows = 0;
if($result)
$rows = mysql_num_rows($result);
if($rows > 0)
{
echo "\nUser exists in database for event.";
//while used to cycle through users incase 2 or more users share the same email address.
while($row = mysql_fetch_object($result))
{
updateuservote( $row->user_id, $eventid, $decision, 1);
updateeventmessage($row->user_id, $eventid, $decision, $firstline);
}
}
else
echo "\nUser does not exist in database for event.";
}
function updatevoteemail($email, $eventid, $decision, $firstline)
{
//get each userid for the one email address for the one event(there may be more than one userid for one address)
$query = "SELECT user_id
FROM (
calendar_users
INNER JOIN groupusers ON calendar_users.user_id = groupusers.userid
)
INNER JOIN calendar_events ON groupusers.groupid = calendar_events.cat
WHERE calendar_users.email = '".$email."'
AND id =".$eventid;
$result = mysql_query($query);
$rows = mysql_num_rows($result);
if($rows > 0)
{
echo "\nUser exists in database for event.";
//while used to cycle through users incase 2 or more users share the same email address.
while($row = mysql_fetch_object($result))
{
updateuservote( $row->user_id, $eventid, $decision, 0);
updateeventmessage($row->user_id, $eventid, $decision, $firstline);
}
}
else
echo "\nUser does not exist in database for event.";
}
function updateeventmessage($userid, $eventid, $decision, $message)
{
$vstring = decisionstring($decision);
$query = "INSERT INTO message (FromUserID, ToUserID, Subject, Body, IsRead, MessageType, IsSent, GroupID, ReminderID, EventID, Created) VALUES ('".$userid."', 0, '".$vstring." vote', '".$message."', 0, 0, 0, 0, 0, ".$eventid.", NOW())";
mysql_query($query) or die('Error, insert query failed');
}
function updateuservote($userid, $eventid, $decision, $type)
{
echo "\nUserID:".$userid;
$query = "SELECT userid from vote where userid = ".$userid." and eventid = ".$eventid;
$result = mysql_query($query);
$rows = mysql_num_rows($result);
$vstring = decisionstring($decision);
//if user has already voted for event, update existing vote record otherwise add a new record
if($rows > 0)
{
$query = "update vote set decision = ".$decision." where userid = ".$userid." and eventid = ".$eventid;
echo "\nUpdate".$decision;
}
else
{
$query = "insert into vote (UserID, eventid, decision) values (".$userid.", ".$eventid.", ".$decision.")";
echo "\nInsert".$decision;
}
$result = mysql_query($query);
$query = "select title from calendar_events where id = ".$eventid;
$result = mysql_query($query);
$rows = mysql_num_rows($result);
$row = mysql_fetch_object($result);
$subject = "Confirmation of ".$vstring." vote for event";
//type 1 is email
if($type == 0)
{
$body = "Thank you for voting for the event: ".$row->title.".<br><br> Your vote of ".$vstring." has been recorded.<br>";
$query = "INSERT INTO message (FromUserID, ToUserID, Subject, Body, IsRead, MessageType, IsSent, GroupID, ReminderID, EventID, Created) VALUES (0, '".$userid."', '".$subject."', '".$body."', 0, 1, 0, 0, 0, 0, NOW())";
}
//type 2 is sms
if($type == 1)
{
$title = ereg_replace("[^A-Za-z0-9[:space:]]", "", $row->title);
$body = "Thank you for voting for the event: ".$title.". Your vote of ".$vstring." has been recorded.";
$query = "INSERT INTO message (FromUserID, ToUserID, Subject, Body, IsRead, MessageType, IsSent, GroupID, ReminderID, EventID, Created) VALUES (0, '".$userid."', 'vote for event', '".$body."', 0, 4, 0, 0, 0, 0, NOW())";
echo "\nreply message to user\ntitle:".$row->title;
echo "\nid:".$userid;
echo "\nvs:".$vstring;
echo "\nBody:".$body;
}
mysql_query($query) or die('Error, insert query failed');
}
function decisionstring($decision)
{
if ($decision == 0 )
$vstring = 'No';
if ($decision == 1)
$vstring = 'Yes';
return $vstring;
}
function left($str, $length) {return substr($str, 0, $length);}
function right($str, $length) {return substr($str, -$length);}
?>