From c757506a0a598593115c377a6e4acf399f4d2b83 Mon Sep 17 00:00:00 2001
From: Arun Persaud <arun@nubati.net>
Date: Sun, 21 Jun 2009 23:23:15 -0700
Subject: [PATCH] added option for email digest

you can now select to get emails only every N hours. They are saved in the database and a cron script will send them out.
---
 create_database.sql     |  12 +++++
 digest.php              | 104 ++++++++++++++++++++++++++++++++++++++++
 include/db.php          |  54 +++++++++++++++++++--
 include/functions.php   |  79 ++++++++++++++++--------------
 include/preferences.php |  70 +++++++++++++++++++++++++--
 update_db.php           |  42 ++++++++++++++++
 6 files changed, 315 insertions(+), 46 deletions(-)
 create mode 100644 digest.php
 create mode 100644 update_db.php

diff --git a/create_database.sql b/create_database.sql
index 339c337..40a008a 100644
--- a/create_database.sql
+++ b/create_database.sql
@@ -465,3 +465,15 @@ create table user_openids (
   user_id int not null,
   index (user_id)
 );
+
+DROP TABLE IF EXISTS `digest_email`;
+create table digest_email (
+  `id` int(11) NOT NULL auto_increment,
+  `email` varchar(255) default null,
+  `create_date` timestamp NOT NULL default '0000-00-00 00:00:00',
+  `content` text,
+  UNIQUE KEY `id` (`id`),
+  index (email)
+);
+
+
diff --git a/digest.php b/digest.php
new file mode 100644
index 0000000..c93117a
--- /dev/null
+++ b/digest.php
@@ -0,0 +1,104 @@
+<?php
+error_reporting(E_ALL);
+
+include_once("config.php");                /* needs to be first in list, since other includes use this */
+include_once("./include/output.php");      /* html output only */
+include_once("./include/db.php");          /* database only */
+include_once("./include/functions.php");   /* the rest */
+
+/* make sure that user has set all variables in config.php */
+config_check();
+
+/* open the database */
+if(DB_open()<0)
+  exit();
+
+/* only callable via cron or CLI */
+if(isset($_SERVER['REMOTE_ADDR']))
+  exit();
+
+/* get userid for users that have digest set != digest-off */
+$users = DB_get_digest_users();
+
+global $defaulttimezone;
+foreach($users as $uid)
+  {
+    // get local time
+
+    $zone  = DB_get_user_timezone($uid);
+    date_default_timezone_set($zone);
+    $time = (int)(date("H"));
+
+    // calculate mod by digest-time
+    $PREF = DB_get_PREF($uid);
+    switch($PREF['digest'])
+      {
+      case 'digest-off':
+	break;
+      case 'digest-1h':
+	$every = 1;
+	break;
+      case 'digest-2h':
+	$every = 2;
+	break;
+      case 'digest-3h':
+	$every = 3;
+	break;
+      case 'digest-4h':
+	$every = 4;
+	break;
+      case 'digest-6h':
+	$every = 6;
+	break;
+      case 'digest-12h':
+	$every = 12;
+	break;
+      case 'digest-24h':
+	$every = 24;
+	break;
+      }
+
+    // make default time to send email 18:00
+    if( ( ($time-18) % $every) == 0 )
+      {
+	$email = DB_get_email('userid',$uid);
+
+	// get messages
+	$messages = DB_get_digest_message_by_email($email);
+
+	// add them together
+	if(sizeof($messages))
+	  {
+	    $text = array();
+	    $i=0;
+	    foreach($messages as $mess )
+	      {
+		$text[$i] = $mess[1]."\n++++++++++++++++++++++++++++++++++++++++++++++++++++++\n";
+		$i++;
+	      }
+
+	    $text = implode("\n",$text);
+
+	    // add header, footer, sent out
+	    $name    = DB_get_name('userid',$uid);
+	    $header  = "Hello $name\n\nHere is a digest of the latest emails:\n\n";
+
+	    $footer  = "\nHave a nice day\n".
+	      "   your E-Doko digester\n\n".
+	      "-- \n".
+	      "You can change your mail delivery mode in the preference menu.\n".
+	      'web: http://doko.nubati.net   '.
+	      'help: http://wiki.nubati.net/EmailDoko   '.
+	      'bugs: http://wiki.nubati.net/EmailDokoIssues';
+
+	    $subject = "$EmailName Digest";
+
+	    sendmail($email,$subject,$header.$text.$footer);
+	  }
+
+	// delete all messages
+	foreach($messages as $mess )
+	  DB_digest_delete_message($mess[0]);
+      }
+  } /* end foreach users */
+?>
\ No newline at end of file
diff --git a/include/db.php b/include/db.php
index b8b184d..cc40f67 100644
--- a/include/db.php
+++ b/include/db.php
@@ -114,6 +114,12 @@ function DB_query_array_all($query)
   return $result;
 }
 
+function DB_get_version()
+{
+  $version = DB_query_array('SELECT version FROM Version');
+  return $version[0];
+}
+
 function DB_get_passwd_by_name($name)
 {
   $r = DB_query_array("SELECT password FROM User WHERE fullname=".DB_quote_smart($name)."");
@@ -700,6 +706,7 @@ function DB_get_PREF($myid)
   /* set defaults */
   $PREF['cardset']		= 'english';
   $PREF['email']		= 'emailnonaddict';
+  $PREF['digest']               = 'digest-off';
   $PREF['autosetup']		= 'no';
   $PREF['sorting']		= 'high-low';
   $PREF['open_for_games']	= 'yes';
@@ -716,13 +723,18 @@ function DB_get_PREF($myid)
 	{
 	case 'cardset':
 	  /* licence only valid until then */
-	  if($pref[1]=="altenburg" && (time()-strtotime( "2009-12-31 23:59:59")<0) )
-	    $PREF["cardset"]="altenburg";
+	  if($pref[1]=='altenburg' && (time()-strtotime( '2009-12-31 23:59:59')<0) )
+	    $PREF['cardset']='altenburg';
 	  break;
 
 	case 'email':
-	  if($pref[1]=="emailaddict")
-	    $PREF["email"]="emailaddict";
+	  if($pref[1]=='emailaddict')
+	    $PREF['email']='emailaddict';
+	  break;
+
+	case 'digest':
+	  if($pref[1])
+	    $PREF['digest'] = $pref[1];
 	  break;
 
 	case 'autosetup':
@@ -1155,4 +1167,38 @@ function DB_get_number_of_tricks($gameid,$position)
   $r = DB_query_array("SELECT COUNT(winner) FROM Trick Where game_id='$gameid' and winner='$position'");
   return $r[0];
 }
+
+function DB_digest_insert_email($To,$message)
+{
+  DB_query("INSERT INTO digest_email VALUES (NULL,".DB_quote_smart($To).",NULL,".DB_quote_smart($message).")");
+  return;
+}
+
+function DB_get_digest_users()
+{
+  $users = array();
+
+  $result = DB_query("SELECT user_id FROM User_Prefs WHERE pref_key='digest' and value <> 'digest-off'");
+  while($r = DB_fetch_array($result))
+    $users[]=$r[0];
+
+  return $users;
+}
+
+function DB_get_digest_message_by_email($email)
+{
+  $messages = array();
+
+  $result = DB_query("SELECT id,content FROM digest_email Where email='$email'");
+  while($r = DB_fetch_array($result))
+    $messages[]=$r;
+
+  return $messages;
+}
+
+function DB_digest_delete_message($id)
+{
+  DB_query("Delete from digest_email where id='$id'");
+}
+
 ?>
\ No newline at end of file
diff --git a/include/functions.php b/include/functions.php
index 3e21e6d..43fbb24 100644
--- a/include/functions.php
+++ b/include/functions.php
@@ -58,51 +58,56 @@ function mymail($uid,$subject,$message)
 {
   global $EmailName;
 
-  /* check if user wants email right away or if we should save it in
-   * the database for later delivery
-   */
-  if(0)
+  /* do we send the email right away or save it in the database? */
+  $send_now = 1;
+
+  /* add standard header and footer */
+  $subject = "$EmailName".$subject;
+
+  /* standard goodbye */
+  $footer  = "\nHave a nice day\n".
+    "   your E-Doko service department\n\n".
+    "-- \n".
+    "You can change your mail delivery mode in the preference menu.\n".
+    'web: http://doko.nubati.net   '.
+    'help: http://wiki.nubati.net/EmailDoko   '.
+    'bugs: http://wiki.nubati.net/EmailDokoIssues';
+
+  if(is_array($uid))
     {
-      /* send to database (not yet implemented)*/
-    }
-  else
-    {
-      /* send email right away */
-
-      /* add standard header and footer */
-      $subject = "$EmailName".$subject;
+      /* send email to more than one person */
 
-      /* standard goodbye */
-      $footer  = "\nHave a nice day\n".
-	"   your E-Doko service department\n\n".
-	"-- \n".
-	"You can change your mail delivery mode in the preference menu.\n".
-	'web: http://doko.nubati.net   '.
-	'help: http://wiki.nubati.net/EmailDoko   '.
-	'bugs: http://wiki.nubati.net/EmailDokoIssues';
+      $header  = "Hello all\n\n";
 
-      if(is_array($uid))
+      foreach($uid as $user)
 	{
-	  /* send email to more than one person */
+	  $all[] = DB_get_email('userid',$user);
+	}
+      $To = implode(",",$all);
+    }
+  else
+    {
+      /* standard greeting */
+      $name    = DB_get_name('userid',$uid);
+      $header  = "Hello $name\n\n";
 
-	  $header  = "Hello all\n\n";
+      $To = DB_get_email('userid',$uid);
 
-	  foreach($uid as $user)
-	    {
-	      $all[] = DB_get_email('userid',$user);
-	    }
-	  $To = implode(",",$all);
-	}
-      else
-	{
-	  /* standard greeting */
-	  $name    = DB_get_name('userid',$uid);
-	  $header  = "Hello $name\n\n";
+      /* check if user wants email right away or if we should save it in
+       * the database for later delivery
+       */
 
-	  $To = DB_get_email('userid',$uid);
-	}
+      $PREF = DB_get_PREF($uid);
+      if( $PREF['digest'] != 'digest-off' )
+	$send_now = 0;
+    }
 
-      sendmail($To,$subject,$header.$message.$footer);
+  if($send_now)
+    sendmail($To,$subject,$header.$message.$footer);
+  else
+    {
+      /* store email in database */
+      DB_digest_insert_email($To,$message);
     }
 }
 
diff --git a/include/preferences.php b/include/preferences.php
index 4abaf3b..8b3937d 100644
--- a/include/preferences.php
+++ b/include/preferences.php
@@ -23,6 +23,7 @@ $changed_sorting      = 0;
 $changed_openforgames = 0;
 $changed_vacation     = 0;
 $changed_openid       = 0;
+$changed_digest       = 0;
 
 display_user_menu($myid);
 
@@ -172,6 +173,24 @@ if(myisset("notify"))
       }
   }
 
+if(myisset("digest"))
+  {
+    $digest=$_REQUEST['digest'];
+    if($digest != $PREF['digest'])
+      {
+	/* check if we already have an entry for the user, if so change it, if not create new one */
+	$result = DB_query("SELECT * from User_Prefs".
+			   " WHERE user_id='$myid' AND pref_key='digest'" );
+	if( DB_fetch_array($result))
+	  $result = DB_query("UPDATE User_Prefs SET value=".DB_quote_smart($digest).
+			     " WHERE user_id='$myid' AND pref_key='digest'" );
+	else
+	  $result = DB_query("INSERT INTO User_Prefs VALUES(NULL,'$myid','digest',".
+			     DB_quote_smart($digest).")");
+	$changed_digest=1;
+      }
+  }
+
 if(myisset("autosetup"))
   {
     $autosetup = $_REQUEST['autosetup'];
@@ -301,15 +320,56 @@ if($PREF['email']=="emailaddict")
     echo "            <option value=\"emailaddict\" selected=\"selected\">less emails</option>\n";
     echo "            <option value=\"emailnonaddict\">lots of emails</option>\n";
   }
- else
-   {
-     echo "            <option value=\"emailaddict\">less email</option>\n";
-     echo "            <option value=\"emailnonaddict\" selected=\"selected\">lots of email</option>\n";
-   }
+else
+  {
+    echo "            <option value=\"emailaddict\">less email</option>\n";
+    echo "            <option value=\"emailnonaddict\" selected=\"selected\">lots of email</option>\n";
+  }
 echo "          </select>";
 if($changed_notify) echo "changed";
 echo " </td></tr>\n";
 
+echo "        <tr><td>Digest:          </td><td>\n";
+echo "          <select id=\"digest\" name=\"digest\" size=\"1\">\n";
+
+$selected = "selected=\"selected\"";
+echo "            <option value=\"digest-off\"";
+if($PREF['digest']=="digest-off") echo $selected;
+echo ">digest off</option>\n";
+
+echo "            <option value=\"digest-1h\" ";
+if($PREF['digest']=="digest-1h") echo $selected;
+echo ">every hour</option>\n";
+
+echo "            <option value=\"digest-2h\" ";
+if($PREF['digest']=="digest-2h") echo $selected;
+echo ">every 2h</option>\n";
+
+echo "            <option value=\"digest-3h\" ";
+if($PREF['digest']=="digest-3h") echo $selected;
+echo ">every 3h</option>\n";
+
+echo "            <option value=\"digest-4h\" ";
+if($PREF['digest']=="digest-4h") echo $selected;
+echo ">every 4h</option>\n";
+
+echo "            <option value=\"digest-6h\" ";
+if($PREF['digest']=="digest-6h") echo $selected;
+echo ">every 6h</option>\n";
+
+echo "            <option value=\"digest-12h\"";
+if($PREF['digest']=="digest-12h") echo $selected;
+echo ">every 12h</option>\n";
+
+echo "            <option value=\"digest-24h\"";
+if($PREF['digest']=="digest-24h") echo $selected;
+echo ">every 24h</option>\n";
+
+echo "          </select>";
+if($changed_digest) echo "changed";
+echo " </td></tr>\n";
+
+
 echo "        <tr><td>Autosetup:          </td><td>\n";
 echo "          <select id=\"autosetup\" name=\"autosetup\" size=\"1\">\n";
 if($PREF['autosetup']=="yes")
diff --git a/update_db.php b/update_db.php
new file mode 100644
index 0000000..140dece
--- /dev/null
+++ b/update_db.php
@@ -0,0 +1,42 @@
+<?php
+error_reporting(E_ALL);
+
+include_once("config.php");                /* needs to be first in list, since other includes use this */
+include_once("./include/db.php");          /* database only */
+include_once("./include/functions.php");   /* the rest */
+
+/* make sure that user has set all variables in config.php */
+config_check();
+
+/* open the database */
+if(DB_open()<0)
+  exit();
+
+/* only callable via cron or CLI */
+if(isset($_SERVER['REMOTE_ADDR']))
+  exit();
+
+$old_version = DB_get_version();
+$current_version = 1;
+
+if($old_version < $current_version)
+  echo "Will upgrade your database now:\n";
+else
+  echo "You are up to date (version ${current_version}), nothing to do.\n";
+
+switch($old_version)
+  {
+  case 0:
+    /* add database for digesting */
+    DB_query("CREATE TABLE digest_email (".
+	     " `id` int(11) NOT NULL auto_increment,".
+	     " `email` varchar(255) default null,".
+	     " `create_date` timestamp NOT NULL default '0000-00-00 00:00:00',".
+	     " `content` text,".
+	     " UNIQUE KEY `id` (`id`),".
+	     " index (email))");
+    DB_query("UPDATE Version set version=1");
+    echo "Upgraded to version 1.\n";
+  }
+
+?>
\ No newline at end of file
-- 
2.25.1