May 132010
 

Kunena is a common freely available add-on for the Joomla content management system that gives you the ability to add a message board or forum to your joomla website, with moderation, threads, document uploads, and many other features. The only main feature that seems to be missing is the ability to send all users a regular digest of postings made over a certain period of time. Daily digest functionality exists in other forum software, like Yahoo! Groups and others, and a number of people on the Kunena message boards have talked about wanting such a feature.

I recently helped a local user group migrate from a Yahoo! Groups page to their own website running Joomla for content management and Kunena for forum functionality. They really like the daily digest option Yahoo! offered, so I figured out a way to implement that in the Kunena forum software.

Disclaimer: I’m not a Joomla developer, and this solution has really nothing to do with Joomla. I’m a Linux systems administrator, so my preferred method of approaching this problem was to write a script that would query the database directly, finding the posts made in the last 24 hours, putting them in an html page, and then emailing that page to all the registered users. So this solution is not as simple as ‘install this joomla module’, or ‘check this configuration option’. But it does work very well.

First, I wanted to give the users the ability to opt-out of the email if they wished. Rather than modify one of the existing joomla tables to store this opt-out option, I created a new mysql table:

mysql> describe digest;

+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| username      | varchar(100) | NO   | PRI | NULL    |       |
| receivedigest | char(5)      | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
2 rows in set (0.25 sec)

Here is the sql used to create this table:

CREATE TABLE `digest` (
`username` varchar(100) NOT NULL,
`receivedigest` char(5) NOT NULL,
PRIMARY KEY  (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The thinking behind this is that everyone is going to get the digest by default. If they choose to opt-out, I will put their username in this table with a value of ‘no’ for the receivedigest field. If they change their mind and want it again, I can either erase them from the table, or change the value to ‘yes’.

Now, you need to create the page that people will use to manage their preferences. I created a directory called “custom” underneath the main directory of my Joomla install to store custom php scripts that are called within Joomla. Here is the first, digest.php:

<html><body>
<?php
global $my;
$username = "$my->username";
$dbusername="SQLUSER";
$password="SQLPASSWORD";
$database="SQLDATABASE";
$dbserver="SQLSERVER";
$dbtable="digest";

### / Initial mysql connection
mysql_connect($dbserver,$dbusername,$password) or die (" Can't connect");
@mysql_select_db($database) or die( "Unable to select database");

$query = "SELECT * FROM $dbtable where username = '$username';";
$query_result=mysql_query($query);
$query_num=mysql_numrows($query_result);
$i=0;
$result="yes";
while ($i < $query_num)
{
 $result="";
 $result=mysql_result($query_result,$i,"receivedigest");
 $i++;
}

if ( $result == "yes" )
{
 $checked="checked";
}
else
{
 $checked="";
}

print "Hello $username!<br><br>";
?>

<form name="digest" id="digest" action="custom/digest_submit.php" method="POST">
<input type="hidden" name="username" value="<?php print "$username" ?>">
<input <? echo "$checked"; ?> name="receive_digest" TYPE="CHECKBOX" VALUE="yes">Check this to receive the daily digest via email<br>
<br><input type="submit" value="Submit"/>

</body>
</html>

Once the user has made their decision, they submit the form which submits to this page:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html lang="en" xml:lang="en">
<head>
<META HTTP-EQUIV=Refresh CONTENT="3; URL=http://website">
</head>
<?php
$dbusername="SQLUSER";
$password="SQLPASSWORD";
$database="SQLDATABASE";
$dbserver="SQLSERVER";
$dbtable="digest";

mysql_connect($dbserver,$dbusername,$password) or die ("Sorry! I couldn't connect to the database! Please try again later");
@mysql_select_db($database) or die( "Unable to select database");

# User submitted variables
$insert_date=date("Y-m-d H:i:s");
$count=1;

$username= mysql_real_escape_string($_POST["username"]);
$receive_digest= mysql_real_escape_string($_POST["receive_digest"]);
if ( $receive_digest == "" )
{
 $receive_digest = "no";
}

$insert = "REPLACE INTO $dbtable ( username, receivedigest ) VALUES ( '$username','$receive_digest')";
$result=mysql_query($insert);
mysql_close();

print "<h2>Preferences Updated.</h2>";
?>

Now that you have a preference page, it’s time to actually write the program that find the daily posts and sends the email. Here it is:

#!/bin/bash
TMPDIR=/tmp/digest.$$
rm -Rf ${TMPDIR}
mkdir -p ${TMPDIR}
outfile="${TMPDIR}/digest.html"
outfile2="${TMPDIR}/digest2.html"
email_file="${TMPDIR}/email"
today=`date --date "yesterday" +%Y-%m-%d`
mysql_line="mysql -s -uSQLUSER -pSQLPASSWORD -hSQLSERVER SQLDATABASE"

echo "<html>
<body>
<h2>Message Board Digest for ${today}</h2>
You are receiving this email because you are subscribed to the daily digest of the message board. To change your subscription, log into <a href=\"http://website\">website</a> and click \"Daily Digest Options\" from the User Menu.
<br>
<hr>
<table border=1>
<tr>
<td>
Message Date
</td>
<td>
Posted By
</td>
<td>
Subject
</td>
</tr>
" > ${outfile}

echo "select a.catid,a.thread,from_unixtime(a.time),a.name,a.subject,b.message from jos_fb_messages a,jos_fb_messages_text b where a.id = b.mesid;" | ${mysql_line} | tr "\t" "|" | sed s/"\\\t"/"\&nbsp;"/g | sed s/"\\\n"/"<br>"/g | while read line;
do
 catid=`echo ${line} | cut -d"|" -f1`
 msgid=`echo ${line} | cut -d"|" -f2`
 msgdate=`echo ${line} | cut -d"|" -f3`
 user=`echo ${line} | cut -d"|" -f4`
 subject=$(echo ${line} | cut -d"|" -f5 |  sed -r s/"\\\\"/""/g)
 message=$(echo ${line} | cut -d"|" -f6 |\
 sed -r s/"url\]"/"url]\n"/g |\
 sed -r s/"\[\/url\]"/"<\/a>"/g |\
 sed -r s/"\[url=(.+)\]"/"<a href=\"\\1\">"/g |\
 sed -r s/"img\]"/"img]\n"/g |\
 sed -r s/"\[\/img\]"/"\">"/g |\
 sed -r s/"\[img\]"/"<img src=\""/g |\
 sed -r s/"\[b\]"/"<b>"/g |\
 sed -r s/"\[\/b\]"/"<\/b>"/g |\
 sed -r s/"\\\\"/""/g)
 if [[ "${msgdate}" =~ ${today}.+ ]]; then
  echo "<tr><td>${msgdate}</td><td>${user}</td><td><a href=\"#${msgid}\">${subject}</a></td></tr>" >> ${outfile}
  echo "<hr><a name=\"${msgid}\">${msgdate} from ${user}</a><br><b><a href=\"http://website/message-board/${catid}/${msgid}\">${subject}</a></b><br>${message}<br><br>" >> ${outfile2}
 fi
done

echo "<hr>" >> ${outfile2}
echo "</table><br>" >> ${outfile}
cat ${outfile2} >> ${outfile}
echo "</body></html>" >> ${outfile}

# figure out who to send the digest to
# for some reason, I stored the table that indicates whether or not a user wants to get the digest in a
# different database than the joomla site was stored in. The first database is "DB1", the joomla database is "DB2"
# modify accordingly for your setup
echo "select DB2.jos_users.email  from DB2.jos_users LEFT JOIN DB1.digest on DB2.jos_users.username = DB1.digest.username where (DB1.digest.receivedigest <> 'no' or DB1.digest.receivedigest IS NULL) and DB2.jos_users.username <> 'admin';" | ${mysql_line} > ${TMPDIR}/list_to_email

for item in `cat ${TMPDIR}/list_to_email`
do
 # append SMTP header
 # replace SENDTO with the appropriate To:
 echo "HELO myserverhostname.com
 MAIL FROM: admin@website
 RCPT TO: SENDTO
 DATA
 From: Daily Digest <admin@website>
 To: SENDTO
 Subject: Daily Digest for $today
 MIME-Version: 1.0
 Content-Type: text/html; charset=us-ascii
 Content-Transfer-Encoding: 7bit" | sed "s/SENDTO/${item}/g" > ${email_file}
 # need an extra newline to conform to HTTP protocol
 # this separates the header from the content
 echo "" >> ${email_file}
 cat ${outfile} >> ${email_file}
 # Tell the mail server we are done
 echo ".
quit" >> ${email_file}
 echo "sending to ${item}"
 cat ${email_file} | /usr/bin/nc localhost 25 1> /dev/null
 sleep 1
done
rm -Rf ${TMPDIR}

This script gets every Joomla user (except those who have opted out) and emails them the html file created for the last day of messages. I run this out a user’s cron sometime after midnight (since it’s querying for yesterday). And it works and everyone is happy!
Still todo:

  • Handle the formatting in the body of the ‘psuedo-html’ that kunena uses (BBCode)
  • Make the opt-out form more AJAX-y
  • More direct links from the digest into individual threads and users

I know this isn’t the easiest thing to implement, but if you’re not that familiar with Linux, find someone who can help you set this up. Until Kunena comes out with a quicker way to do this, this is probably your best bet.

Jan 192010
 

UPDATE 2010-12-27: The latest version of MythTV (0.24) will not work with the instructions in this article. If you want to use XBMC’s builtin myth:// protocol support, you have to stay with MythTV .23. The XBMC devs are working on making XBMC a generic frontend to as many backend DVRs as possible (including MythTV – more info here) so there is hope for the future. In the meantime, you have a couple of different options. You can use Mythbox plugin for XBMC. If you’re on MythTV .24, make sure you get the latest development build, as the stable release only supports the .23 protocol. Run this from linux to get the latest code:

# hg clone https://mythbox.googlecode.com/hg/ mythbox

The other option is to have your XBMC play the recorded files directly, over a drive share. Assuming you’re using a hardware capture card, the files are already in mpeg format, which XBMC can play just fine. They are just named strangely. Read the guide at http://www.mythtv.org/wiki/Mythlink.pl to learn how to make nicely named symlinks directly to your recorded files so that you can browse them in XBMC and play them.


XBMC (formerly the Xbox Media Center) is an amazing cross-platform application for all your media viewing needs. At it’s base, it’s a program to watch video files and dvds, play mp3s, and view pictures. With the plugins and addons from the community, you can stream YouTube, listen to internet radio, sing karaoke, watch TED talks, and much, much more. There is even progress being made on XBMC being a ‘standard’ frontend for a number of different DVR systems. One of those systems is MythTV. XBMC has native MythTV support, which means that, out of the box, XBMC can connect to your MythTV server, view your list of recorded programs, and play them. You can also delete recordings, browse the program guide, and watch live TV (although that feature is pretty experimental). However, as good as XBMC is, configuring it correctly to make it the best it can be for MythTV support can be a little tricky. Here are the steps I have taken to make my XBMC install good enough to score high on the Wife Acceptance Factor.

This howto assumes you have:

  • A working MythTV backend system
  • You’ve configured a MythTV frontend before, preferably on a box different from your backend. This isn’t required, but if you’ve made this work, then your MythTV database is setup correctly to accept remote connections.
  • A box that’s ready to be your MythTV frontend. This howto will assume this is a Windows box, but these instructions work the same for Mac/Linux/XBOX.

Short Version (tl;dr):

  • Install the latest XBMC from xbmc.org. As of this writing, the latest release for Windows is 9.11. Or, if you’re really brave, grab the latest SVN snapshot. NOTE: there is a MythTV-specific bug in version 9.11: if you’re watching a recording that is longer than one hour, after the first hour the skip forward and skip backward will work very intermittently, if at all. This has been fixed in subversion. So if you really need this, use a nightly build.
  • Configure XBMC to hit your MythTV backend, and verify this works
  • Modify the XBMC skin to show the plots of recorded shows
  • Optional: configure a Streamzap remote to work under Windows

Long Version:

Here is my setup:

  • MythTV server running version .21 (yes I know I need to upgrade) on Fedora 8 (again, I know I need to upgrade). IP address is 10.0.0.1 and fully qualified domain name is fileserv.haederfamily.org. It’s a home-built AMD Athlon XP 2400+ with 512MB RAM and 2TB of disk.
  • Frontend system is a MiniITX-based system running Windows XP Pro SP3. This is connected to my 100MB switched home network.

After you successfully install XBMC, run the program to see the default startup screen. The latest versions of XBMC use the skin ‘Confluence’ as the default, so we’re assuming that’s the case here. Other skins work as well, but the modification you need to see the recorded show plot is different depending upon the skin layout.

Go into the Videos section and select ‘Add Source’ from the menu. Hit enter on the name (where is says <None>) and type this:

myth://username:password@10.0.0.1/

where ‘username’ is the username you use to connect to your MythTV mysql database and ‘password’ is the password. If you didn’t change anything from the default MythTV install, this is probably ‘mythtv’ and ‘mythtv’. Hit enter here, and arrow down ‘Enter a name for this media source’. Name it ‘MythTV’ and click ‘OK’. You should now see a ‘MythTV’ in your video sources. Highlight that and hit enter, and if all goes well, you should see a screen like this:

And when you select ‘TV Shows’ it should look something like this:

and when you click “America’s Funniest Home Videos” hopefully you’ll see a list of recorded episodes:

Select an episode, hit enter, and it should start playing.

If it doesn’t, and you get some sort of a popup XBMC-style error, then odds are you have a DNS problem. This plagued me the first time I set this up. In your MythTV server settings, you have to identify the fully qualified domain name of your MythTV server. When you first setup MythTV, this is set to ‘localhost’. You MUST change this in order to get any kind of a MythTV frontend to work on your network. Once you have this set, you need to make sure that the fully qualified domain name for your MythTV server resolves to the correct IP address when you query it from your frontend. So, in my example, I need to make sure that when I ping ‘fileserv.haederfamily.org’ from my Windows XP frontend, it resolves to ‘10.0.0.1’. If this is not the case, you must correct your DNS or add entries to your host file in order for this to work. You can’t go any further until you do this.

Once you’re in a position where you can watch recorded episodes, you only have 2 steps left: change the skin so the plots of recorded shows are displayed, and possibly setup a StreamZap remote.

The MythTV native support for XBMC works very well, but it is lacking some functionality compared to the full-blown MythTV linux frontend. One thing you might notice initially is that when you highlight a recorded episode, you don’t see any plot or episode information. Don’t blame XBMC support; the fault lies with the skin. The plot information for recordings is available through the XBMC MythTV interface, but the skin has to display that data somewhere. It seems that none of the skins available for XBMC (at least not the ones that ship with the ‘official’ release) display this data anywhere. This is a relatively simple fix, and it makes the MythTV experience on XBMC much, much better.

They key lies in the file ViewsFileMode.xml, in the directory XBMC/skin/Confluence/720p. Now, I am not an expert on XMBC skinning, and in fact I got this addition from a posting on the xbmc.org forums. So this works for me, try it, it will probably work for you.

Open up that file in an editor, go to line 163, and insert this block of text:

<!-- plot mod -->
 <control type="textbox">
 <posx>0</posx>
 <posy>10</posy>
 <width>344</width>
 <height>300</height>
 <label>$INFO[ListItem.Plot]</label>
 </control>
<!-- end plot mod -->

The key is the variable $INFO[ListItem.Plot]. This references the ‘Plot’ (or synopsis) or the recorded show. This information is available through the XBMC interface, but the skin does not show it by default. This addition will put the plot information in a 344x300px box in the upper right hand corner of the screen, which seems to be the perfect spot for the Confluence skin. If you don’t want to modify your file, download a copy of mine and replace your ViewsFileMode.xml. REMEMBER: this file gets overwritten everytime you upgrade XBMC, so keep a backup copy!

Once you’ve made this change, restart XBMC and you should see a screen that looks something like this:

And there you go. Plot information for your recordings.

A few questions:

  • Why isn’t this in a skin somewhere by default?
  • What other MythTV variables do I have access to? I’d love to show the length of an episode somewhere, or the episode title. I’m sure that’s documented somewhere, but I can’t see to find it.

Ok, now that that part is done, let’s configure a remote control for XBMC. I prefer the StreamZap remote, which is a remote designed for PCs. The IR receiver has a USB interface, and it works great with Windows and Linux (through LIRC, specifically the lirc_streamzap kernel module). Under Windows, the StreamZap software is responsible for handling keypresses and actions for an application. However, currently the StreamZap software does not ‘natively’ support  XBMC. The basic keys work (arrow keys, OK key, power key) but not much else. In order to really get the StreamZap to work effectively, you need to use a free application called EventGhost. EventGhost will recognize the StreamZap keypresses, and will pass the appropriate command along to XBMC. Rather than walking you through screen shots of how to configure EventGhost, you can just download my EventGhost config file for StreamZap and XBMC. Install EventGhost, run it, select File -> Open, and open my StreamZap.xml file. Then fire up XBMC and try your remote. It should work like a charm!

Note that the default software that comes with the Streamzap Remote will interfere with EventGhost! Make sure you stop that software, remove it from your PC’s startup settings, and then run EventGhost.

Please leave me feedback on if this worked for you, and any other tips or tricks you have regarding XBMC and MythTV.