Calendar "Email Reminder" Good for SMS or Cellphon

Are you missing a feature in Group-Office. You can always try to request it!

Moderator: Developers

partypop

Calendar "Email Reminder" Good for SMS or Cellphon

Postby partypop » Tue Jul 06, 2004 9:45 am

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.
stephenward_hw
Posts: 23
Joined: Mon Jun 14, 2004 9:43 pm
Location: Calgary, Alberta, Canada

Postby stephenward_hw » Wed Jul 07, 2004 5:31 pm

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
partypop
Posts: 1
Joined: Thu Jul 08, 2004 4:33 am

Postby partypop » Thu Jul 08, 2004 4:35 am

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
dare2dreamer
Posts: 9
Joined: Fri Jun 11, 2004 7:37 am

ME TOO!

Postby dare2dreamer » Thu Jul 08, 2004 8:08 am

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
ursus

Me Too

Postby ursus » Fri Jul 09, 2004 6:46 pm

Please I would like the code an instructions to!!
Thank you in advance
Ursus

Sorry forgot to give address

Postby Ursus » Fri Jul 09, 2004 8:20 pm

Sorry forgot to give address
jose@adicora.net
thanx
partypopp

tried

Postby partypopp » Sat Jul 10, 2004 5:12 pm

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?
stephenward_hw
Posts: 23
Joined: Mon Jun 14, 2004 9:43 pm
Location: Calgary, Alberta, Canada

Postby stephenward_hw » Sat Jul 10, 2004 5:27 pm

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
Guest

Postby Guest » Sat Jul 10, 2004 8:02 pm

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.
stephenward_hw
Posts: 23
Joined: Mon Jun 14, 2004 9:43 pm
Location: Calgary, Alberta, Canada

Postby stephenward_hw » Sun Jul 11, 2004 4:40 am

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
Last edited by stephenward_hw on Sun Jul 11, 2004 4:45 am, edited 1 time in total.
User avatar
ttimalsina
Posts: 56
Joined: Thu Apr 22, 2004 5:23 pm
Location: San Francisco, California, USA

Postby ttimalsina » Sun Jul 11, 2004 4:44 am

Steve,
It's been working great. Thank you for sharing your code to us.

Regards,
Tim
Eric
Posts: 23
Joined: Wed Nov 10, 2004 1:12 am
Location: Munich, Germany
Contact:

Code for the field in the profile sheets

Postby Eric » Wed Nov 10, 2004 11:10 pm

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
stephenward_hw
Posts: 23
Joined: Mon Jun 14, 2004 9:43 pm
Location: Calgary, Alberta, Canada

Postby stephenward_hw » Wed Nov 10, 2004 11:42 pm

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
stephenward_hw
Posts: 23
Joined: Mon Jun 14, 2004 9:43 pm
Location: Calgary, Alberta, Canada

Postby stephenward_hw » Thu Nov 11, 2004 12:28 am

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]
Eric
Posts: 23
Joined: Wed Nov 10, 2004 1:12 am
Location: Munich, Germany
Contact:

Wow

Postby Eric » Fri Nov 12, 2004 12:24 am

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

Return to “Feature requests”

Who is online

Users browsing this forum: No registered users and 3 guests

cron