#!/bin/bash

# trac-reminder - sends pretty HTML email with a list of open Trac tickets
# Copyright (C) 2006 Bearstech - http://bearstech.com
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA

trac="/var/www/"
conf="$trac/conf/trac.ini"
db="$trac/db/trac.db"
usercnt=0
mailidcnt=0

#Read username & corresponding mailid from files "username" & "mailid"
#Only draw back of this method is that, username & corresponding mailid 
#should be in the same line in there respective files.
#Read from file needs some optimization

while read myline
do
   user[$usercnt]=$myline
   usercnt=`expr $usercnt + 1`
done < username

while read line
do
    mail[$mailidcnt]=$line
    mailidcnt=`expr $mailidcnt + 1`
done < mailid


# Figure out URL base. Quote ':' for reuse in regex subst later. Strip trailing slash(es).
url=`sed -e 's:\::\\\\\::g'  -e 's:/*$::' -ne 's:^ *url *= *::p' $conf`

#Fetch open tickets from the database for each username & write it to the file 
#reminder.html & send mail to that user.

for ((i=0; i < usercnt ; i++))
do
    unset tickets
    tickets=`sqlite3 "$db" "select id,owner,status,summary from ticket where status='new' and owner like '${user[$i]}%'"`
    [ x"$tickets" == x ] && continue
    IFS=$'\n'
    unset t
    unset html
    for t in $tickets; do
	row=`echo -n "$t" | sed -e 's:^\([0-9]\+\):<a href="'$url'/ticket/\1">#\1</a>:' -e 's:|:</td>\n    <td>:g'`
              html="$html  <tr>
	  <td>$row</td>
	</tr>
    "
    done

>reminder.html
cat >> reminder.html << EOF
<html>
<table>
  <tr>
    <th>Ticket</th>
    <th>Owner</th>
    <th>Status</th>
    <th>Description</th>
         </tr>
$html
</table>
</html>
EOF

#Call your mail client application here.I have a crude java script that is a smtp client application
#Below is how I call it

#java SendMulti ${mailid[$i]} messagefile reminder.html subjectofmail

done
