Hello
for whom it may concern, I coded an E-Mail reminder for the calendar
( http://doc.otrs.org/2.0/en/html/c1409.html#application-calendar ) events
I'm running this script everyday at 8am on cronjob... you may want to
run it each 5 minutes, for example, but then you'd need to change the
comparison of time (currently it's sending if today=event_date)
#!/usr/bin/perl
use DBI;
########## START CONFIG ##########
#maybe the MySQL information could be read from Kernel/Config.pm, but I don't know how to do it
$mysqluser = '';
$mysqlpass = '';
$mysqldb = '';
$mailprog = "/usr/sbin/sendmail";
$from = 'OTRS Events Notifier ';
########### END CONFIG ###########
$time=time();
$dia=(localtime($time))[3];
$mes=(localtime($time))[4]+1;
$ano=(localtime($time))[5]+1900;
$db_handle = DBI->connect("dbi:mysql:database=$mysqldb;host=localhost:port number;user=$mysqluser;password=$mysqlpass") or die "Couldn't connect to database: $DBI::errstr\n";
$sql = "SELECT ce.start_time,up.preferences_value,ce.title,ce.content FROM calendar_event ce, calendar_event_involved cei, user_preferences up WHERE ce.id=cei.event_id AND cei.user_id=up.user_id AND up.preferences_key LIKE 'UserEmail'";
$statement = $db_handle->prepare($sql) or die "Couldn't prepare query '$sql': $DBI::errstr\n";
$statement->execute() or die "Couldn't execute query '$sql': $DBI::errstr\n";
$reminders=0;
while (@Row = $statement->fetchrow()) {
if ((substr($Row[0], 0, 4) == $ano) && (substr($Row[0], 5, 2) == $mes) && (substr($Row[0], 8, 2) == $dia)) {
$reminders++;
$subject="[otrs] REMINDER - " . $Row[2];
$body = "Reminder for Row[0]\n\n";
$body .= "Title: $Row[2]\n\n";
$body .= "Lembrete: $Row[3]";
open(MAIL,"|$mailprog -t") || die("Error opening $mailprog");
print MAIL "From: $from\n";
print MAIL "To: $Row[1]\n";
print MAIL "Subject: $subject\n\n";
print MAIL "$body\n";
close(MAIL);
}
}
