Page 1 of 2

Calendar "Email Reminder" Good for SMS or Cellphon

Posted: Tue Jul 06, 2004 9:45 am
by partypop
Calendar Email Reminder Good for SMS or Cellphone Reminders with the appropriate email.

So, if I setup my email address in the program to be 1234568@messaging.nextel.com

the program will send a reminder to my cellphone, to alert me of an upcoming appointment.

An extremely useful feature, that yahoo has on their site.

Posted: Wed Jul 07, 2004 5:31 pm
by stephenward_hw
I have implemented this for our users in the form of a some additional fields in the GO database and some php code that runs on a cron schedule outside of php. It is not in any publishable format (a bit quick and dirty). I can send you the code and some instructions if you are interested.

Cheers

/Steve

Posted: Thu Jul 08, 2004 4:35 am
by partypop
stephenward_hw wrote:I have implemented this for our users in the form of a some additional fields in the GO database and some php code that runs on a cron schedule outside of php. It is not in any publishable format (a bit quick and dirty). I can send you the code and some instructions if you are interested.

Cheers

/Steve
Wow, thanks for offering to give me the code, and instructions for the
email reminder feature!

I would very much appreciate it!

my email is partypop69@yahoo.com

ME TOO!

Posted: Thu Jul 08, 2004 8:08 am
by dare2dreamer
I'd appreciate it if you could send me a copy of your code as well. I've been trying to figure out how to hack up some cron scrips for GO for a while now.

A few examples would really help.

toe@dare2dream.com

Me Too

Posted: Fri Jul 09, 2004 6:46 pm
by ursus
Please I would like the code an instructions to!!
Thank you in advance

Sorry forgot to give address

Posted: Fri Jul 09, 2004 8:20 pm
by Ursus
Sorry forgot to give address
jose@adicora.net
thanx

tried

Posted: Sat Jul 10, 2004 5:12 pm
by partypopp
stephenward_hw wrote:I have implemented this for our users in the form of a some additional fields in the GO database and some php code that runs on a cron schedule outside of php. It is not in any publishable format (a bit quick and dirty). I can send you the code and some instructions if you are interested.

Cheers

/Steve
Steve sent me the info. I tried to add the code where he noted.. the instructions were good. except, I could not find the files you mentioned.
By using text search through all the group-office files, I was able to find some of the related code in calendar.install.sql and groupoffice.sql

I am using v2.05, maybe the files are different?

Posted: Sat Jul 10, 2004 5:27 pm
by stephenward_hw
Sorry, the SQL file that I sent you was meant to indicate the changes to the backend database. When I did this, I did it outside of the GO framework and just added the fields that I needed manually using webmin. This is a bit crude and could definitely be integrated better. You should not need to modify the GO code for this change. However, you will also need to manually fill the contact_email field with the appropriate email address for the users that would like sms notification as well as the processreminders field should be set to 1 for all of the calendars desired to tell the external php program to process the calendar.

I have recently modified my version of GO to allow access to the contact_email field so users can modify this. I have not changed it to access the process reminders field on the calendar yet. This should be fairly easy.

Hope this helps

Cheers

/Steve

Posted: Sat Jul 10, 2004 8:02 pm
by Guest
I would like a copy of your code as well... daniel@everyvideogame.com ... perhaps it would be a good idea to publish the code here.

Posted: Sun Jul 11, 2004 4:40 am
by stephenward_hw
I have been asked to publish the code here so here goes. Please pardon the length of this post. Please note that this was a pretty quick fix and it shows. I have akso modified my version of GO to allow editing of the contact_email field from the profile sheets. I can make these changes available as well. I hope that this is of use.

First the cron file which runs it. I run this file every 5 minutes but this frequency is not important. I use lynx to execute the php pages which just seemed to be an easy way to develop this.

Code: Select all

#! /bin/bash

logger "Processing reminders"
lynx -dump http://www.hewlett-ward.com/groupofficereminder.php >> /var/log/groupofficereminder.log
lynx -dump http://www.hewlett-ward.com/groupofficetodoreminder.php >> /var/log/groupofficereminder.log

Here is a list of the current 2.05 table structures with the fields that I added. This was done manually using webmin and not via the install.sql file in the calendar module and user modules. Note that this also means the users sms email address which goes in contact_email must be manually entered via whatever tool is of preference for editing mysql tables (I used webmin which was effective). The new fields are indicated with '*****>'.

This also applies to the processreminders field which determines whether to process the reminders for a particular calendar. This field defaults to 1 or yes for all newly created calendars and it would be easy to issue an update statement to deal with the existing ones.

Change Summary

cal_calendars:
field processreminders is set to 1 when you want to process the
reminders in this calendar and 0 to disable this say for group calendars.
*****> `processreminders` tinyint(4) default '1',

cal_events
field reminder_sent indicates whether the reminder has been sent and
tries to avoid notifying twice. I believe that this is superseded by
same field in cal_events_calendars which was to deal with events that
had more than one calendar.
*****> `reminder_sent` tinyint(4) default '0',

cal_events_calendars
field reminder_sent to indicate reminder has been sent.
*****> `reminder_sent` tinyint(4) default '0',
field next_reminder is updated by the php code in
groupofficereminder.php and is used to trigger the reminder because the
built in logic was removing the reminder time once it had occurred and
before I had sent the message.
*****> `next_reminder` int(11) default '0',

cal_todos
field reminder_sent to track notification of todos in
groupofficetodoreminder.php
*****> `reminder_sent` tinyint(4) default '0',

users
field contact_email to give the users contact email address and thus
it can be different from main email address. This is manually set in
the database at this time (ie no gui yet).
*****> `contact_email` varchar(255) default NULL,

Code: Select all


--
-- Table structure for table `cal_calendars`
--

CREATE TABLE `cal_calendars` (
  `id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `acl_read` int(11) NOT NULL default '0',
  `acl_write` int(11) NOT NULL default '0',
  `name` varchar(100) NOT NULL default '',
  `start_hour` tinyint(4) NOT NULL default '0',
  `end_hour` tinyint(4) NOT NULL default '0',
*****>  `processreminders` tinyint(4) default '1',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

--
-- Table structure for table `cal_events`
--

CREATE TABLE `cal_events` (
  `id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `acl_read` int(11) NOT NULL default '0',
  `acl_write` int(11) NOT NULL default '0',
  `start_time` int(11) NOT NULL default '0',
  `end_time` int(11) NOT NULL default '0',
  `all_day_event` enum('0','1') NOT NULL default '0',
  `contact_id` int(11) NOT NULL default '0',
  `name` varchar(100) NOT NULL default '',
  `description` text NOT NULL,
  `location` varchar(100) NOT NULL default '',
  `background` varchar(7) NOT NULL default '',
  `repeat_type` enum('0','1','2','3','4','5') NOT NULL default '0',
  `repeat_forever` enum('0','1') NOT NULL default '0',
  `repeat_every` tinyint(4) NOT NULL default '0',
  `repeat_end_time` int(11) NOT NULL default '0',
  `mon` enum('0','1') NOT NULL default '0',
  `tue` enum('0','1') NOT NULL default '0',
  `wed` enum('0','1') NOT NULL default '0',
  `thu` enum('0','1') NOT NULL default '0',
  `fri` enum('0','1') NOT NULL default '0',
  `sat` enum('0','1') NOT NULL default '0',
  `sun` enum('0','1') NOT NULL default '0',
  `month_time` enum('0','1','2','3','4','5') NOT NULL default '0',
  `reminder` int(11) NOT NULL default '0',
  `ctime` int(11) NOT NULL default '0',
  `mtime` int(11) NOT NULL default '0',
*****>    `reminder_sent` tinyint(4) default '0',
  PRIMARY KEY  (`id`),
  KEY `start_time` (`start_time`),
  KEY `end_time` (`end_time`),
  KEY `repeat_end_time` (`repeat_end_time`),
  KEY `acl_read` (`acl_read`),
  KEY `acl_write` (`acl_write`)
) TYPE=MyISAM;

--
-- Table structure for table `cal_events_calendars`
--

CREATE TABLE `cal_events_calendars` (
  `calendar_id` int(11) NOT NULL default '0',
  `event_id` int(11) NOT NULL default '0',
*****>    `reminder_sent` tinyint(4) default '0',
*****>    `next_reminder` int(11) default '0',
  KEY `calendar_id` (`calendar_id`,`event_id`)
) TYPE=MyISAM;


--
-- Table structure for table `cal_todos`
--

CREATE TABLE `cal_todos` (
  `id` int(11) NOT NULL default '0',
  `user_id` int(11) NOT NULL default '0',
  `contact_id` int(11) NOT NULL default '0',
  `ctime` int(11) NOT NULL default '0',
  `mtime` int(11) NOT NULL default '0',
  `start_time` int(11) NOT NULL default '0',
  `due_time` int(11) NOT NULL default '0',
  `completion_time` int(11) NOT NULL default '0',
  `remind_time` int(11) NOT NULL default '0',
  `remind_style` enum('0','1') NOT NULL default '0',
  `status` tinyint(4) NOT NULL default '0',
  `priority` enum('0','1','2') NOT NULL default '0',
  `res_user_id` int(11) NOT NULL default '0',
  `name` varchar(50) NOT NULL default '',
  `description` text NOT NULL,
  `location` varchar(50) NOT NULL default '',
  `background` varchar(6) NOT NULL default '',
*****>    `reminder_sent` tinyint(4) default '0',
  PRIMARY KEY  (`id`),
  KEY `user_id` (`user_id`,`res_user_id`),
  KEY `remind_time` (`remind_time`)
) TYPE=MyISAM;


--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL default '0',
  `username` varchar(50) NOT NULL default '',
  `password` varchar(64) NOT NULL default '',
  `authcode` varchar(20) NOT NULL default '',
  `first_name` varchar(50) NOT NULL default '',
  `middle_name` varchar(50) NOT NULL default '',
  `last_name` varchar(50) NOT NULL default '',
  `initials` varchar(10) NOT NULL default '',
  `title` varchar(10) NOT NULL default '',
  `sex` enum('M','F') NOT NULL default 'M',
  `birthday` date NOT NULL default '0000-00-00',
  `email` varchar(100) NOT NULL default '',
  `company` varchar(50) NOT NULL default '',
  `department` varchar(50) NOT NULL default '',
  `function` varchar(50) NOT NULL default '',
  `home_phone` varchar(20) NOT NULL default '',
  `work_phone` varchar(20) NOT NULL default '',
  `fax` varchar(20) NOT NULL default '',
  `cellular` varchar(20) NOT NULL default '',
  `country` varchar(50) NOT NULL default '',
  `state` varchar(50) NOT NULL default '',
  `city` varchar(50) NOT NULL default '',
  `zip` varchar(10) NOT NULL default '',
  `address` varchar(100) NOT NULL default '',
  `homepage` varchar(100) NOT NULL default '',
  `work_address` varchar(100) NOT NULL default '',
  `work_zip` varchar(10) NOT NULL default '',
  `work_country` varchar(50) NOT NULL default '',
  `work_state` varchar(50) NOT NULL default '',
  `work_city` varchar(50) NOT NULL default '',
  `work_fax` varchar(20) NOT NULL default '',
  `acl_id` int(11) NOT NULL default '0',
  `date_format` varchar(20) NOT NULL default 'd-m-Y H:i',
  `time_format` varchar(10) NOT NULL default '',
  `thousands_seperator` char(1) NOT NULL default '.',
  `decimal_seperator` char(1) NOT NULL default ',',
  `currency` char(3) NOT NULL default '',
  `mail_client` tinyint(4) NOT NULL default '1',
  `logins` int(11) NOT NULL default '0',
  `lastlogin` int(11) NOT NULL default '0',
  `registration_time` int(11) NOT NULL default '0',
  `max_rows_list` tinyint(4) NOT NULL default '15',
  `timezone` tinyint(4) NOT NULL default '0',
  `DST` enum('0','1','2') NOT NULL default '0',
  `start_module` varchar(50) NOT NULL default '',
  `language` varchar(20) NOT NULL default '',
  `theme` varchar(20) NOT NULL default '',
  `first_weekday` tinyint(4) NOT NULL default '0',
*****>    `contact_email` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `id` (`id`)
) TYPE=MyISAM;

Here are the two php files that are executed. Basically, it is one for the calendar events and another to process todo reminders. The output of these programs is captured in a log file for debugging at /var/log/groupofficereminders.log. You could edit out these debugging lines if you like or use logrotate to truncate the logs so they do not grow too big. You will also need to change the connect line at the beginning to reflect your site.

groupofficereminder.php

Code: Select all

<?php


$link = mysql_connect("postgres","groupoffice","password") or die ("Could not connect : " . mysql_error());
echo "Connected";
mysql_select_db("groupoffice") or die ("could not select database");

$query="Select distinct e.id,e.name,e.description,e.location,e.reminder,e.start_time,u.contact_email,timezone,ce.calendar_id,r.remind_time,ce.next_reminder,ca.name as calendarname  from cal_events e,users u,cal_events_calendars ce,cal_calendars ca, cal_reminders r where ca.user_id=u.id and e.id=ce.event_id and e.id=r.event_id and ce.calendar_id=ca.id and ce.reminder_sent=0 and ca.processreminders=1 and reminder>0";
$result=mysql_query($query) or die ("Query failed : " . mysql_error());
$now=time();

echo "<p>Appointment Check Current Time is $now ". strftime("%r",$now) . "</p>";

echo "<table>\n";
while ($line=mysql_fetch_array($result,MYSQL_ASSOC)) {
	echo "\t<tr>\n";
	$tzoffset=$line["timezone"]*3600;
	$remindertime=$line["next_reminder"]+$tzoffset;	
	$remindertimefromreminders=$line["remind_time"]+$tzoffset;	
	$localstart=$line["start_time"]+$tzoffset;
	if (strftime("%r",$localstart) == strftime("%r",$remindertime)) {
		$remindertime=$line["next_reminder"]-$line["reminder"]+$tzoffset;
	}
//	$remindertime=$line["start_time"]-$line["remind_time"]+$tzoffset;
	foreach ($line as $col_value) {
		echo "\t\t<td>$col_value</td>\n";
	}
	echo "\t\t<tr><td>Reminder Time: $remindertime ". strftime("%c",$remindertime) . "</td><tr>";
	$elapsed=$remindertime-$now-300;
	echo "\t\t<tr><td>Time To Reminder: $elapsed</td></tr>";
	echo "\t</tr>\n";
	echo "\t<tr><td colspan=10>Local Start: '$localstart' ".strftime("%c",$localstart)."</td></tr>";
	echo "\t<tr><td colspan=10>Reminder Time: '$remindertime' ".strftime("%c",$remindertime)."</td></tr>";
	echo "\t<tr><td colspan=10>Next Reminder from reminders table: '$remindertimefromreminders' ".strftime("%c",$remindertimefromreminders)."</td></tr>";
	if ($elapsed <= 0 and $line["next_reminder"] > 0 and $localstart > $now) {
		$remindermin=($localstart-$now)/60;
		$message="Remember appointment for " . $line["name"] . "[" . $line["description"] . "] " . $line["location"] . " in $remindermin minutes at ".strftime("%r",$localstart);
		mail($line["contact_email"],"Appointment reminder for ".$line["name"],$message);
		$updateqry="update cal_events_calendars set reminder_sent=1 where event_id=" . $line["id"] . " and calendar_id=". $line["calendar_id"];
		
		echo "\t<tr><td colspan=10>$message</td></tr>";
		echo "\t<tr><td colspan=10>$updateqry</td></tr>";
		mysql_query($updateqry);
	}
	if ($remindertime < 0 || ($line["remind_time"] > $line["next_reminder"])) {
		$sql="update cal_events_calendars set reminder_sent=0,next_reminder=".$line["remind_time"]." where event_id=".$line["id"];
		echo "\t<tr><td colspan=10>$sql</td></tr>";
		mysql_query($sql);
	}
	echo "\t<tr><td colspan=10>**********</td></tr>";
}
echo "</table>\n";

mysql_free_result($result);

mysql_close($link);



?>
groupofficetodoreminder.php

Code: Select all

<?php


$link = mysql_connect("postgres","groupoffice","password") or die ("Could not connect : " . mysql_error());
echo "Connected";
mysql_select_db("groupoffice") or die ("could not select database");

$query="Select distinct e.id,e.name,e.description,e.location,e.remind_time,e.start_time,u.contact_email,timezone from cal_todos e,users u, cal_calendars ca where e.res_user_id=u.id and e.res_user_id=ca.user_id and reminder_sent=0 and ca.processreminders=1 and remind_time>0 and completion_time=0";
$result=mysql_query($query) or die ("Query failed : " . mysql_error());
$now=time();

echo "<p>Todo Check Current Time is $now ". strftime("%r",$now) . "</p>";

echo "<table>\n";
while ($line=mysql_fetch_array($result,MYSQL_ASSOC)) {
	echo "\t<tr>\n";
	$remindertime=$line["remind_time"]+$line["timezone"]*3600;
	$localstart=$line["start_time"]+$line["timezone"]*3600;
	foreach ($line as $col_value) {
		echo "\t\t<td>$col_value</td>\n";
	}
	echo "\t\t<td>Reminder Time: $remindertime ". strftime("%c",$remindertime) . "</td>";
	$elapsed=$remindertime-$now;
	
	echo "\t\t<td>Elapsed: $elapsed</td>";
	echo "\t</tr>\n";
	if ($elapsed <= 0) {
		$remindermin=($line["start_time"]-$line["remind_time"])/60;
		$message="Remember todo " . $line["name"] . "[" . $line["description"] . "] " . $line["location"] . " in $remindermin minutes at ".strftime("%r",$localstart);
		mail($line["contact_email"],"Todo reminder for ".$line["name"],$message);
		$updateqry="update cal_todos set reminder_sent=1 where id=" . $line["id"];
		
		echo "\t<tr><td colspan=10>$message</td></tr>";
		echo "\t<tr><td colspan=10>$updateqry</td></tr>";
		mysql_query($updateqry);
	}
	
	echo "\t<tr><td colspan=10>Local Start: '$localstart' ".strftime("%c",$localstart)."</td></tr>";
	echo "\t<tr><td colspan=10>Reminder Time: '$remindertime' ".strftime("%c",$remindertime)."</td></tr>";
	echo "\t<tr><td colspan=10>**********</td></tr>";
}
echo "</table>\n";

mysql_free_result($result);

mysql_close($link);



?>

Cheers

/Steve

Posted: Sun Jul 11, 2004 4:44 am
by ttimalsina
Steve,
It's been working great. Thank you for sharing your code to us.

Regards,
Tim

Code for the field in the profile sheets

Posted: Wed Nov 10, 2004 11:10 pm
by Eric
Dear stephanward_hw,

Could you publish the code for the field in the profile sheets, as you mentioned?

It would be great, thank you for your good work!!!
Eric

Posted: Wed Nov 10, 2004 11:42 pm
by stephenward_hw
Are you using 2.05 or are you using the 2.09 series. I am in the process of porting my changes to 2.05 to 2.09 but it might take a bit to get it all there. If you are using 2.05, I can give you the change for that.

Cheers

/Steve

Posted: Thu Nov 11, 2004 12:28 am
by stephenward_hw
I have looked into this aspect and there is not much difference in the code from 2.05 to 2.09. Basically you need to make 4 changes.

1. Change to <go-directory>/configuration/profile.inc Place this in the table somewhere. I do it at around line 136

Code: Select all


	<tr height="25">

		<td align="right" nowrap><?php echo $strContactEmail; ?>:&nbsp;</td>

		<td><input type="text" class="textbox"  name="contactemail" size="40" value="<?php echo $profile["contact_email"]; ?>" maxlength="255"></td>

	</tr>




2. Change to <go-directory>/classes/base/sql.users.class.inc at line 219

Code: Select all


  function update_profile($user_id, $first_name, $middle_name, $last_name,
      $initials, $title, $sex, $birthday, $email,
      $work_phone, $home_phone, $fax, $cellular,
      $country, $state, $city, $zip, $address, $company,
      $work_country, $work_state, $work_city, $work_zip,
      $work_address, $work_fax, $homepage, $department,
      $function,$contactemail='')
  {
    $middle_name = trim($middle_name);

    $sql = "UPDATE users SET first_name='$first_name', ".
      "middle_name='$middle_name', last_name='$last_name', ".
      "initials='$initials', title='$title', sex='$sex', ".
      "birthday='$birthday', email='$email', work_phone='$work_phone',".
      "home_phone='$home_phone', fax='$fax', cellular='$cellular',".
      "country='$country', state='$state', city='$city', zip='$zip', ".
      "address='$address',company='$company', department='$department',".
      "function='$function', work_country='$work_country', ".
      "work_state='$work_state', work_city='$work_city',".
      "work_zip='$work_zip', work_address='$work_address', ".
      "work_fax='$work_fax', homepage='$homepage', contact_email='$contactemail' WHERE id='$user_id'";

The rest is the same


3. Change <go-directory>/configuration/index.php

Around line 166

Code: Select all

		$contactemail = smart_addslashes($_POST["contactemail"]);
Around line 199

Code: Select all


      if (!$GO_USERS->update_profile($GO_SECURITY->user_id, $first_name,
      					$middle_name, $last_name, $initials, $title, $_POST["sex"], $birthday,
      					$email, $work_phone, $home_phone, $fax, $cellular, $country,
      					$state, $city, $zip, $address, $company, $work_country,
      					$work_state, $work_city, $work_zip, $work_address, $work_fax,
      					$homepage,  $department, $function,$contactemail))


4. Change to <go-directory>/language/common/en.inc (or whatever language) add

$strContactEmail = "Contact Email";


[/code]

Wow

Posted: Fri Nov 12, 2004 12:24 am
by Eric
Dear stephenward_hw,

Wonderful, that you looked at the forum and that you replied with the code! Thank you very much !!!

But ...

I have all done, what you recommended, but id doesn't work. (But I have to say, that I even get not the pop up reminders in the browser (from the beginning). In your xxx-reminder.php's you mentioned "mail". I have set the group office on smtp, which works well, but can I you then "mail"?

(By the way: in 2.09 cal_todo is td_todo, I changed this)

At the moment I start the xxx-reminder.php directly in the url for testing. If an event in the past has a reminder, I can use the xxx-reminder.php even hours later, it should than be work, ok?

Do you have any possibility to help me in this matter?

Furthermore I get following: after deleting an user as admin I get the error: header not loadable or so, because he is already loaded. Before making your changes it was ok.

It would be great to hear from you again.

Best wishes
Eric