#!/usr/bin/perl

# Sends an email with today's appointment for each user that has one
# I put this in cron.daily, so that it's sent at 4am every day

use Net::SMTP;
use DBI;
$dbh = DBI->connect('dbi:mysql:webcalendardb','user','pass');

$time = time();
$month = ((localtime($time))[4] + 1);
$day = (localtime($time))[3];
$month = "0" . $month if ($month < 10);
$day = "0" . $day if ($day < 10);
$year = 1900 + (localtime($time))[5];
$today= $year . $month . $day;
$sql = "SELECT DISTINCT cal_login FROM webcal_entry INNER JOIN webcal_entry_user USING(cal_id) WHERE cal_date LIKE '$today' ORDER BY cal_login";
$sth = $dbh->prepare($sql);
$sth->execute;
while (@row=$sth->fetchrow_array) {
  $login = $row[0];
  $sql = "SELECT webcal_entry.cal_id,cal_time,cal_name,cal_login FROM webcal_entry INNER JOIN webcal_entry_user USING(cal_id) where cal_date = '$today' AND cal_login='$login' ORDER BY cal_login";
  $sth2 = $dbh->prepare($sql);
  $sth2->execute;
  $sth2->bind_columns(undef, \$cal_time, \$cal_time, \$cal_name, \$cal_login);
  $subject = "Today's appointments $today for $login";
  print "$subject:\n";
  while($sth2->fetch()) {
     $body .= "$cal_time $cal_name\n";
     print "$cal_time $cal_name\n";
  }
  $sth2->finish();
  $email = $login . "\@example.com";
    $smtp = Net::SMTP->new('localhost');
    $smtp->mail("root\@example.com");
    $smtp->to($email);
    $smtp->data();
    $smtp->datasend("To: $email\n");
    $smtp->datasend("From: Web Calendar <root\@example.com>\n");
    $smtp->datasend("Subject: $subject\n");
    $smtp->datasend("\n");
    $smtp->datasend($body);
    $smtp->dataend();
    $smtp->quit;
}
$sth->finish();
$dbh->disconnect;