#! /usr/bin/perl -w

# RealizationEngine
my $version = "2.1.0h";

# Copyright Realization Systems, Inc., 2002, 2003
# http://www.RealizationSystems.com/
# P.O. Box 1, Plains, Texas 79355, U.S.A.

# RealizationEngine is the trademark of Realization Systems, Inc. 

# This program is not free software; you can modify it under the 
# terms of the License but you may not redistribute without a 
# license to do so from Realization Systems, Inc.
# 
# This program is distributed WITHOUT ANY WARRANTY; without even 
# the implied warranty of MERCHANTABILITY or FITNESS FOR A 
# PARTICULAR PURPOSE.
# 
# You should have received a copy of the License
# along with this program; if not, write to Realization Systems,
# Inc., 407 N. York Ave., Hagerman, NM  88232.


# srand (time ^ $$ ^ unpack "%32L", 'ps axww | gzip');
# open URANDOM, "/dev/urandom";
# my $rand_seed = unpack "%32L", <URANDOM>;
# close URANDOM;
# srand ($rand_seed);
$|=1;

# sendmail location on this box (ignore if you're on Windows)
my $sendmail_path = ''; # this is a dummy assignment to keep from filling the log with warnings.
$sendmail_path = "/usr/sbin/sendmail";

# what kind of mail transfer agent (MTA) are we gonna use? sendmail or Blat (Windows)
# my $MTA = "sendmail"; # uncomment this line for *nix systems
# $MTA = "blat"; # uncomment this line for Windows with Blat installed

use CGI qw(:cgi :form :html);
use DBI;
use POSIX;
use Digest::MD5 qw(md5_hex);
use Mail::Mailer;
# use Benchmark;
# use vars qw/$t0 $t1 $td $tf/;
use strict;

use vars qw/$folder_session_age $age %users_groups %allowed_table $URI $current_system_time $session_age $currently_online $url_folder $page $pages $linkfolder $cookie_domain $session $delta_time $timestamp $session_timeout $expandmessage $expand $sth $query $message_userid $thread_limit $thread_style $op $message_background $status $searchresult $searchquery $download $user_thread_style $mark @marks $op_status $root_folder_name $folder $folderid @folder $where $dbh $query $sth @ary @approved_tags $day_old_message_background $warm_message_time $warm_message_background $fresh_message_background $fresh_message_time $day_old_title_background $warm_title_background $fresh_title_background $cgi_url $otherw $groupmember $groupw $groupw $ownerid $open $edit_interval $name $nth_level_thread_order $contents $user_table $title_tag $page_background $domain_name $notification_address $notification_message $privacy_statement $upload_limit_MB $in_block $in_table $page_footer $title $allow_search $recent_interval $notification_message $site_name $cookie_name $dbpassword $db_user $dsn $first_level_thread_order $folderURI $foldername $groupid $groupr $otherr $parentid $cookie $our_userid $username $error $foldergroupid $group $groupname $parentfolderid @group @other @folder_array %value $doc_root $limit $page $home_dir/;

use vars qw/$total $todays $weeks $sessions/; # declarations for sub countthread 
use vars qw/$leader $leaders/; # declarations for sub quick_jump_menu
$name = qq{};

my $data_root = $doc_root = $ENV{"DOCUMENT_ROOT"};
$data_root =~ s/html/data/;
require "$data_root/settings.re";
($dsn, $db_user, $dbpassword, $cgi_url) = &setup();

$home_dir = $doc_root;
$home_dir =~ s/\/html//;

$first_level_thread_order = "DESC";
$nth_level_thread_order = "ASC";

# connect to database
$dbh = DBI->connect ($dsn, $db_user, $dbpassword);
# my $failed=0;
# $dbh = DBI->connect ($dsn, $db_user, $dbpassword, { $failed=1 });
unless (defined ($dbh) ) 
{
  print header();
  print qq{Failed to connect to database},br;
  print qq{Database may be down for maintenance.},br;
  print qq{Try again later, or contact the site administrator if problem persists.},br;
  exit(0);
}

# After connection, grab the site-specific settings
$query = qq{SELECT value FROM settings WHERE name="user_table"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($user_table) = my @ary = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="cookie_name"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($cookie_name) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="domain_name"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($domain_name) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="site_name"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($site_name) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="notification_address"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($notification_address) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="notification_message"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($notification_message) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="recent_interval"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($recent_interval) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="edit_interval"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($edit_interval) = $sth->fetchrow_array ();
$edit_interval = $edit_interval * 3600; # convert edit interval from hours to seconds

$query = qq{SELECT value FROM settings WHERE name="thread_limit"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($thread_limit) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="thread_style"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($thread_style) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="page_background"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($page_background) = $sth->fetchrow_array () || "#FFFFFF";

$query = qq{SELECT value FROM settings WHERE name="message_background"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($message_background) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="fresh_message_time"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($fresh_message_time) = $sth->fetchrow_array ();
$fresh_message_time = $fresh_message_time*24*3600;

$query = qq{SELECT value FROM settings WHERE name="fresh_message_background"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($fresh_message_background) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="fresh_title_background"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($fresh_title_background) = $sth->fetchrow_array ();
$allowed_table{"fresh_title_background"} = $fresh_title_background if $fresh_title_background =~ /^#\w{6}/;

$query = qq{SELECT value FROM settings WHERE name="warm_message_time"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($warm_message_time) = $sth->fetchrow_array ();
$warm_message_time = $warm_message_time*24*3600;

$query = qq{SELECT value FROM settings WHERE name="warm_message_background"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($warm_message_background) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="warm_title_background"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($warm_title_background) = $sth->fetchrow_array ();
$allowed_table{"warm_title_background"} = $warm_title_background if $warm_title_background =~ /^#\w{6}/;

$query = qq{SELECT value FROM settings WHERE name="day_old_message_background"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($day_old_message_background) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="day_old_title_background"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($day_old_title_background) = $sth->fetchrow_array ();
$allowed_table{"day_old_title_background"} = $day_old_title_background 
      if $day_old_title_background =~ /^#\w{6}/;

#$query = qq{SELECT value FROM settings WHERE name="guest_font_color"};
#$sth = $dbh->prepare ($query);
#$sth->execute ();
#($guest_font_color) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="allow_search"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($allow_search) = $sth->fetchrow_array ();

$query = qq{SELECT value FROM settings WHERE name="title_tag"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($title_tag) = $sth->fetchrow_array ();
$allowed_table{"title_tag"} = $title_tag;

$query = qq{SELECT value FROM settings WHERE name="title"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($title) = $sth->fetchrow_array ();

$title =~ s/\$(\w+)/$allowed_table{$1}/g;

$query = qq{SELECT value FROM settings WHERE name="page_footer"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($page_footer) = $sth->fetchrow_array ();
$page_footer =~ s/\$(\w+)/${$1}/g;

$query = qq{SELECT value FROM settings WHERE name="upload_limit_MB"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($upload_limit_MB ) = $sth->fetchrow_array () || .10; # default to 100 KB
$CGI::POST_MAX=1024 * 1024 * $upload_limit_MB;  # limit uploads to $upload_limit_MB MBs

$query = qq{SELECT value FROM settings WHERE name="P_privacy_statement"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($privacy_statement) = $sth->fetchrow_array ()
     || qq{<b>Privacy Statement:</b><br>}.
         qq{We value our relationship with }.
        qq{our visitors, and will not share or sell your user }.
        qq{information to anyone for any reason <b>ever</b>.};
$page_footer =~ s/\$(\w+)/${$1}/g;

$sth->finish ();

@approved_tags = ("b", # acceptable HTML tag list
                      "i",
                      "font",
                      "a",
                      "blockquote",
                      "li",
                      "ol",
                      "ul",
                      "table",
                      "td",
                      "tr",
                      "th",
                      "u",
                      "sup",
                      "sub",
                      "p",
                      "em",
                      "strong",
                      "strike",
                      "tt",
                      "div",
                      "ecode",
                      "img",
                      "q",
                      "pre",
                      "span",
                      "embed",
                      "object",
                      "param",
                      "center",
                      "dt",
                      "dd",
                      );

$in_table=0; # decalre $in_table for use in space translation supression
$in_block=0; # decalre $in_block for use in space translation supression

# Check for 'op' or NULL
$op = param('op') || 'NULL';

$status = param('status') || NULL;
$op = 'status' if $status;

# $xstatus = param('xstatus') || NULL;
# $op = 'xstatus' if $xstatus;

# Get search result if search result
$searchresult = param('searchresult') || 0;

# get search query if search query
$searchquery = param("searchquery");

# get download id if download is requested
$download = param('download') || NULL;
$download = int($download) if $download;

# set $user_thread_style to 0 for default
$user_thread_style = 0;

# grab "marked" terms if asked for
$mark = param('mark') || '';
if ($mark)
{
  @marks = split(/,/, $mark);
}

# general declarations to avoid uneccessary waringings
$op_status = '';

# Find name of 'root' folder
$query = qq{SELECT name FROM message WHERE id=1};
$sth = $dbh->prepare ($query);
$sth->execute ();
($root_folder_name) = $sth->fetchrow_array ();
$sth->finish ();
$root_folder_name = 'root' unless $root_folder_name;

$folder = param("folder") || $root_folder_name;
$folder =~ s/^\///; $folder =~ s/\/$//; # clean off any hanging /'s on the front or end 
                                        # of the folder "path"
# if $op eq 'expand', find the folder the message lives in first and set that as our folder
# This will fix things in the case that folder the message resides in has been moved or renamed.
if ( $op eq 'expand' || $op eq 'isolate' )
{
  $expandmessage = int( param('message') ) || 0;
  my $query = qq{SELECT folderid
              FROM message
              WHERE id=$expandmessage};
  my $sth = $dbh->prepare ($query);
  $sth->execute ();
  ($folderid) = $sth->fetchrow_array ();
  $sth->finish ();
  $folder = $root_folder_name."/".regress_folders($folderid);
  chop($folder);
}

@folder = split(/\//, $folder);

my $bread = $folder; # we'll need a copy of $folder to chop into breadcrumbs
my @breadcrumbs; # for bread crumb-style top-navigation
my $breadcrumb_link = ''; # declare the "bread crumb folder" link holder

# parse the folder params to find correct child folder
# we'll do this by itterating through the array created 
# above and then feeding the info into a SELECT
my $where = "level$#folder.name = '$folder[$#folder]' AND ";
my $i = 0; # set $i to 0 just in case
my $query_folderid = "level$#folder.id";
my ($from); # declare $from
my $level;
foreach (reverse(@folder) ) 
{
  $level = $#folder - $i;
  my $next_level = $level - 1;
  $from .= "message level$level, ";
  $where .= qq{level$level.parentid = level$next_level.id AND }.
            qq{level$next_level.name = "$folder[$next_level]" AND }.
            qq{level$level.folder = 'Y' AND }
          if $level > 0;
  $breadcrumbs[$level] = $bread; # assign the currect "bread" to $breadcrumbs[$level]
  $breadcrumb_link = qq{/<a href="$cgi_url?folder=$bread">$folder[$level]</a>}.$breadcrumb_link;
  $bread =~ s/\/$folder[$level]$//; # trim a folder off the end of the $bread
  $i++;
}
# prepend root folder
# my $expandtest = param('expand'); # this is only used to test for the parameter, no where else.
$breadcrumb_link = qq{<a href="$cgi_url?folder=$root_folder_name">$root_folder_name</a>}.$breadcrumb_link
  unless ( $breadcrumb_link =~ /folder=$root_folder_name"/ ); 
$breadcrumb_link =~ s/^\///; # trim off the leading "/" when it happens for the root folder
$from =~ s/,\s$//; # trim last ", " from $from
$where =~ s/AND\s$//; # trim last "AND " from $where
$query = qq{SELECT 
             $query_folderid 
             FROM $from 
             WHERE $where AND level$level.folder='Y'};
# explain_query($query);
# print header(),$query,br; # for diagnosing folder query
# $folder_query = $query;     # store folder query for later use in debugging
$sth = $dbh->prepare ($query);
$sth->execute ();
($folderid) = $sth->fetchrow_array ();
$sth->finish ();

my ($parentfolder); #declare
if ( defined($folderid) ) 
{
  # here's the new method by using the "folder" param passed to the script...
  for ($i=0; $i<$#folder; $i++) 
  {
    $parentfolder .= "/$folder[$i]";
  }
  $parentfolder =~ s/\ /\%20/g if $parentfolder;
  $parentfolder = '' unless $parentfolder;
  if ($folder ne '' && $folder ne $root_folder_name && $parentfolder eq '') 
  {
    $parentfolder = $root_folder_name;
  }
}
if (!defined($folderid) ) 
{
  $folderid = 1;
  $folder = $root_folder_name;
}

$query = qq{SELECT name, parentid, userid, groupid, groupr, groupw, otherr, 
                   otherw, URI, contents, open
            FROM message
            WHERE id=$folderid};
$sth = $dbh->prepare ($query);
$sth->execute ();
($foldername, $parentid, $ownerid, $groupid, $groupr, $groupw, $otherr, $otherw, 
    $folderURI, $contents, $open) = $sth->fetchrow_array ();
$sth->finish ();

# after we know where we're going, we'll grab the user info or log the user in/out in
# the module below. This module cannot be moved higher in the code becuase we have to
# know the folder we're going to, and the owner and group that fold belongs to.
$currently_online = '';
$name = user();

# if the there is no user logged in, we'll assign them as "guest"
$username = 'guest' unless $username;

if ($op eq 'new folder' &&  ($our_userid == $ownerid || $name eq 'root'
    || ($groupmember eq 'Y' && $groupw eq 'Y')) && $open eq 'Y' && $our_userid>0 ) {
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - $folder",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the favicon.ico
  print start_form(-action=>$cgi_url, -method=>"post");
  print_title();
  print_folder();
  print "Welcome back, $name",br;
  print br;
  print qq{<b>Folder name:</b> },textfield(-name=>"foldername"),
        qq{ <font class="navigation" style="color: blue; "><i>Folder names must be }.
        qq{unique within parent.</i></font>},br;
  $query = qq{SELECT groups.name, groups.id FROM groups, members 
              WHERE members.userid=$our_userid AND groups.id = members.groupid 
              GROUP BY groups.name
              ORDER BY groups.name};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  print qq{<table><tr><td>};
  print "<b>Group:</b> <select name=groupname>";
  while ( @ary = $sth->fetchrow_array () ) {
    ($group, $foldergroupid) = @ary;
    print "<option value=$foldergroupid ";
    print "SELECTED" if $foldergroupid == $groupid;
    print ">$group";
    }
  $sth->finish ();
  print "</select>",br;
  print qq{</td><td>};
  print qq{ <font class="navigation" style="color: blue; ">(select the group for the folder to belong to)};
  print qq{</font></td></tr></table>};
  print "<table border=0>";
  print "<tr><td><b>Group Access:</b> </td><td>",checkbox_group(-name=>'group',
                                        -values=>['Read','Write'],
                                        -default=>['Read','Write'],
                                        ),"</td>",
        qq{<td> <font class="navigation" style="color: blue; ">},
        qq{<b>Read</b> = "group members can read folder contents";<br>},
        qq{<b>Write</b> = "group members can create messages in folder"</font></td></tr>};
  print "<tr><td><b>Other Access:</b> </td><td>",checkbox_group(-name=>'other',
                                        -values=>['Read','Write'],
                                        -default=>[],
                                        ),"</td>",
        qq{<td> <font class="navigation" style="color: blue; ">},
        qq{<b>Read</b> = "non-group members can read folder contents";<br>},
        qq{<b>Write</b> = "non-group members can create messages in folder"</font></td></tr>};
  print "</table>";
  print "<b>Folder description:</b> ",
        br,textarea(-name=>"contents", -rows=>9, -columns=>80, wrap=>'virtual'),
        br,qq{ <em>Folder description is optional.</em>},
        qq{<td> <font class="navigation" style="color: blue; ">},
        qq{(The folder contents can be updated by the owner any time.)</font>},br;
  print qq{<input type=hidden name="parentfolderid" value=$folderid>};
  print qq{<input type=hidden name="parentfolder" value="$folder">};
  $folder =~ s/\%20/\ /g; # convert spaces in folder name to esc codes
  print "<input type=hidden name=folder value=\"$folder\">";
  print submit(-name=>'op', -value=>"add folder");
  print ", or ",submit(-name=>'op', -value=>"cancel");
  print "</p>";
  print end_form(),end_html();
  }
elsif ($op eq 'add folder' && ($our_userid == $ownerid || $name eq 'root'
      || ($groupmember eq 'Y' && $groupw eq 'Y'))) {
  $parentfolderid = param('parentfolderid');
  $parentfolder = param('parentfolder');
  # print header(-cookie=>[$cookie]),"folder = $folder",br,"parentfolder = $parentfolder",br;
  $foldername = param('foldername')
    || print redirect(-cookie=>[$cookie], -location=>"$cgi_url?folder=$folder&NO_FOLDER_NAME");
#  $foldername =~ s#</?[^allow_HTML_tags].*?>##gmi;
  unless ( $foldername =~ /^[\w .\-+_?~]+$/ ) {
    print header(-cookie=>[$cookie]),"You used an illegal character in your folder name.";
    print br,qq{allowed characters are A-Za-z0-9 -+_?~. (including spaces)},br;
    $sth->finish ();
    $dbh->disconnect ();
    exit(0);
    }
  $foldername =~ s/\\/\\\\/g;
  $foldername =~ s/"/\\"/g;
  $groupname = param('groupname')
    || print redirect(-cookie=>[$cookie], -location=>"$cgi_url?folder=$folder&NO_GROUP_NAME");
  $contents = param('contents');
#  $contents =~ s#</?[^allow_HTML_tags].*?>##gmi;
  $contents =~ s/\\/\\\\/mg;
  $contents =~ s/"/\\"/mg;
  @group = param('group');
  @other = param('other');
  if ($group[0]) {  $group[0] = 'Y'; } else {  $group[0] = 'N'; }
  if ($group[1]) {  $group[1] = 'Y'; } else {  $group[1] = 'N'; }
  if ($other[0]) {  $other[0] = 'Y'; } else {  $other[0] = 'N'; }
  if ($other[1]) {  $other[1] = 'Y'; } else {  $other[1] = 'N'; }
  # print "@group",br,"@other";
  $error = "SOMETHING";
  if ( $foldername && $groupname ) {
    $query = qq{INSERT into message
                SET
                name="$foldername",
                folder="Y",
                folderid = $parentfolderid,
                groupr="$group[0]",
                groupw="$group[1]",
                otherr="$other[0]",
                otherw="$other[1]",
                groupid="$groupname",
                userid="$our_userid",
                contents="$contents",
                time=date_format(now(), '%Y%m%d%H%i%s'),
                parentid="$parentfolderid"};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    $sth->finish ();
    $error = "NO_ERROR";
    }
  $folder = $parentfolder."/".$foldername;
  $folder =~ s/\ /\%20/g;
  $folder =~ s/'/\\'/g;
  $folder =~ s/\\\\/\\/g;
  print redirect(-cookie=>[$cookie], -location=>"$cgi_url?folder=$folder");
  }
elsif ($op eq 'attr' && $name ne undef && ( $our_userid == $ownerid || $name eq 'root' ) ) {
  $expand = param('expand') || 1;
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - $folder",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print_title();
  print_folder();
  my $linkpath = $folder;
  $linkpath =~ s/\ /%20/g;
  print qq{<a href="$cgi_url?folder=$linkpath">[ abort ]</a>};
  print br;
  print start_form(-action=>$cgi_url, -method=>"post");
  print "<input type=hidden name=\"folder\" value=\"$folder\">\n";
  print "<input type=hidden name=expand value=$expand>";
  print "Rename: ",textfield(-name=>"new_message_name", -value=>"$foldername"),br;

  move_folder(1);

  print "<table cellpadding=0 cellspacing=0><tr><td rowspan=2 valign=top>".
        "Permissions&nbsp;</td>";
  print "<td align=right>Group:</td>";
  print "<td><input type=checkbox name=new_group value=Read ";
  print "CHECKED" if $groupr eq 'Y';
  print ">Read ";
  print "<input type=checkbox name=new_group value=Write ";
  print "CHECKED" if $groupw eq 'Y';
  print ">Write";
  print "</td></tr>";
  print "<tr><td align=right>Other:</td>";
  print "<td><input type=checkbox name=new_other value=Read ";
  print "CHECKED" if $otherr eq 'Y';
  print ">Read ";
  print "<input type=checkbox name=new_other value=Write ";
  print "CHECKED" if $otherw eq 'Y';
  print ">Write";
  print "</td>";
  print "</tr></table>";

  # Change Group -- users can change to another group they are a part of,
  # 'root' can change the folder to any group
  print "Change Group: <select name=new_groupid>";
  print "<option>" if $groupid < 1;
  $query = qq{SELECT groups.id, groups.name
              FROM groups, members
              WHERE members.userid = $our_userid
              AND members.groupid = groups.id
              GROUP BY groups.name
              ORDER BY groups.name};
  $query = qq{SELECT groups.id, groups.name
              FROM groups, members
              GROUP BY groups.name
              ORDER BY groups.name} if $name eq 'root';
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  while ( @ary = $sth->fetchrow_array () ) 
  {
    my ($newgroupid, $groupname) = @ary;
    print "<option value=$newgroupid ";
    print "SELECTED" if $newgroupid eq $groupid;
    print "> $groupname";
  }
  $sth->finish ();
  print "</select>",br;

  # 'root' can change the owner of a folder
  if ($username eq 'root') 
  {
    print "Change Owner: <select name=new_ownerid>";
    print "<option>" if $ownerid < 1;
    $query = qq{SELECT user.id, user.name
                FROM user
                ORDER BY user.name};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    while ( @ary = $sth->fetchrow_array () ) 
    {
      my ($newownerid, $username) = @ary;
      print "<option value=$newownerid ";
      print "SELECTED" if $newownerid eq $ownerid;
      print "> $username";
    }
    $sth->finish ();
    print "</select>",br;
  }
  else { print qq{<input type="hidden" name="new_ownerid" value="$our_userid">}; }

  print "Description: ",br,
        textarea(-name=>"new_contents",
                 -rows=>25, columns=>70,
                 -default=>"$contents",
                 -wrap=>"virtual"),br;
  print "Open: ";
  print radio_group(-name=>'open_state',
                    -values=>['Y','N'],
                    -default=>$open,
                    -linebreak=>0,
                    -labels=>{'Y','Y','N','N'}),br;
# commented out below is open_state confirmation. I cannot do this unless I want to
# first check the open_state of the folder before the confirmation can mean anything.
#   print "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
#   print checkbox_group(-name=>'open_confirm',
#                        -values=>['confirm'],
#                        -default=>'',
#                        -linebreak=>0,
#                        -labels=>{'confirm','confirm change of open status'}),br;
  print submit(-name=>"op", -value=>"update attributes");
  $sth->finish ();
  print end_form(), end_html();
  }
elsif ($op eq 'update attributes' && defined($name)
      && ( $our_userid == $ownerid || $name eq 'root' ) ) 
{
  $folder =~ s/\ /%20/g;
  $expand = param('expand') || 1;
  my $new_message_name = param('new_message_name');
#  $new_message_name =~ s#</?[^allow_HTML_tags].*?>##gmi;
  unless ( $new_message_name =~ /^[\w \.\-_\?~]+$/ ) 
  {
    print header(-cookie=>[$cookie]),"You used an illegal character in your folder name.";
    $sth->finish ();
    $dbh->disconnect ();
    exit(0);
  }
  if ( $new_message_name eq '' ) 
  {
    print header(-cookie=>[$cookie]),"You cannot leave the folder name blank.";
    $sth->finish ();
    $dbh->disconnect ();
    exit(0);
  }
  $new_message_name =~ s/\\/\\\\/gm;
  $new_message_name =~ s/"/\\"/gm;
  my $new_parent = param('new_parent');
  my $new_owner = param('new_ownerid');
  $new_owner =~ s/\\/\\\\/gm;
  $new_owner =~ s/"/\\"/gm;
  my $new_group = param('new_groupid');
  $new_group =~ s/\\/\\\\/gm;
  $new_group =~ s/"/\\"/gm;
  my $new_contents = param('new_contents');
  $new_contents =~ s/\\/\\\\/gm;
  $new_contents =~ s/"/\\"/gm;
  my @new_group = param('new_group');
  my @new_other = param('new_other');
  if ($new_group[0] eq 'Read') {  $new_group[0] = 'Y'; } else {  $new_group[0] = 'N'; }
  if ($new_group[1] eq 'Write') {  $new_group[1] = 'Y'; } else {  $new_group[1] = 'N'; }
  if ($new_other[0] eq 'Read') {  $new_other[0] = 'Y'; } else {  $new_other[0] = 'N'; }
  if ($new_other[1] eq 'Write') {  $new_other[1] = 'Y'; } else {  $new_other[1] = 'N'; }
  $new_parent = 0 if $folderid == 1;
  my $new_open_state = param('open_state');
  my $update = qq{UPDATE message SET 
                  name = "$new_message_name", 
                  parentid = $new_parent, 
                  folderid = $new_parent, 
                  groupr = "$new_group[0]", 
                  groupw = "$new_group[1]", 
                  otherr = "$new_other[0]", 
                  otherw = "$new_other[1]", 
                  userid = $new_owner, 
                  groupid = $new_group, 
                  contents = "$new_contents",
                  open = "$new_open_state"
                  WHERE id = $folderid};
  # print header(), $update,br;
  $folder =~ s/$folder[$#folder]/$new_message_name/;
  print redirect(-cookie=>[$cookie],
                 -location=>"$cgi_url?folder=$folder&expand=$expand");
  $sth = $dbh->prepare ("$update");
  $sth->execute ();
  $sth->finish ();
  print end_html();
}
elsif ($op eq 'new entry' && ( (($otherw eq 'Y' || $our_userid == $ownerid)
      || ($groupmember eq 'Y' && $groupw eq 'Y')) && $open eq 'Y' && $our_userid > 0 ) ) 
{
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - $folder",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico

  my $parent = param('id') || '';
  $expand = param('expand') || 1;

  my ($old_messagename, $old_contents, $old_userid, $old_age, $old_username);
  if ($parent) 
  {
    $query = qq{ SELECT message.name, contents, message.userid,
                ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(time) ) as age
                FROM message
                WHERE message.id=$parent
                };
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    @ary = $sth->fetchrow_array ();
    ($old_messagename, $old_contents, $old_userid, $old_age) = @ary;
    $sth->finish ();
    if ($old_userid > 0) 
    {
      $query = qq{ SELECT user.name
                  FROM user
                  WHERE user.id=$old_userid
                  };
      $sth = $dbh->prepare ($query);
      $sth->execute ();
      @ary = $sth->fetchrow_array ();
      ($old_username) = @ary;
    }
    $sth->finish ();
    unless ($old_messagename =~ /^RE:/) 
    {
      $old_messagename = "RE: ".$old_messagename;
    }
  }

  print start_multipart_form(-action=>$cgi_url, -method=>"post");
  print_title();
  print_folder();
  update_folder_session($folderid);
  print br;
  if( $name ) 
  {
    print "Welcome back, $name",br;
  }
  else 
  {
    print qq{<font color="FF0000"><b>Your entry will be added as "Guest" unless you }.
          qq{<a href="$cgi_url?op=login">login</a></b></font>},br;
  }
  print br;
  if ($parent) 
  {
    print "<table border=1><tr><td>";
      # freshness indicator
      $message_background = $warm_message_background if $old_age < $warm_message_time;
      $message_background = $fresh_message_background if $old_age < $fresh_message_time;
      $message_background = $day_old_message_background if $old_age > $warm_message_time;

      my $title_background = $day_old_title_background if $old_age > $warm_message_time;
      $title_background = $warm_title_background if $old_age < $warm_message_time;
      $title_background = $fresh_title_background if $old_age < $fresh_message_time;

      print qq{<table border=0 cellpadding=3 cellspacing=1>};
      print qq{<tr><td bgcolor="$title_background" }.
            qq{style="border-style:outset; padding-top:1px; border-width: 1px; }.
            qq{ padding-bottom: 1px; background-color: $title_background; ">}.
            qq{<b>$old_messagename</b></td></tr>};
      print qq{<tr><td bgcolor=$message_background>};
      print qq{<font style="font-size=80%; "><b>};
      print qq{by $old_username};
      print qq{</b></font>};
      $URI = escapeHTML("$URI") if $URI;
      print qq{<a href="$URI">link</a> - } if $URI;
      $folder =~ s/\ /%20/g;
      print br;
      print_contents($old_contents);
      print "</td></tr></table>";
      print "</td></tr></table>";
    }
    else 
    {
      $old_messagename = "";
    }
    print "Message title: ",textfield(-name=>"messagename", -value=>"$old_messagename"),br;
    print "Message body:",br,textarea(-name=>"contents", -value=> "", -rows=>"12",
          -columns=>"80", -wrap=>"virtual"),br;
    print "Is there a URL that goes with this message? ",
          textfield(-name=>"URI",size=>50,-value=>'http://'),br;
    print qq{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }.
          qq{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }.
          qq{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }.
          qq{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }.
          qq{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }.
          qq{&nbsp; &nbsp; &nbsp; &nbsp; }.
          qq{<font style="font-size: 75%; "> }.
          qq{(Example: "http://www.seekersoftheredmist.com/")</font>},br;
    # print "&nbsp;&nbsp;&nbsp;&nbsp;Link name for the URL? ", textfield(-name=>"linkname",size=>50),br;
    print "&nbsp;&nbsp;&nbsp;&nbsp;Attachment: ",filefield(-name=>"upload_file",size=>50),br;
    $folder =~ s/\%20/\ /g;
    print "<i>(File uploads limited to $upload_limit_MB MB.)</i>",br;
    print "<input type=hidden name=folder value=\"$folder\">";
    print "<input type=hidden name=folderid value=\"$folderid\">";
    print "<input type=hidden name=parent value=$parent>";
    print qq{<input type="hidden" name="expand" value="$expand">};
    print submit(-name=>'op', -value=>"preview");
    print submit(-name=>'op', -value=>"add entry");
    print " (attachments will not be stored on preview.)";
    print ", or ",submit(-name=>'op', -value=>"cancel"),br;
    print " <i><b>Please allow enough time for uploads to complete if you attach a file.".
          "</b></i>",br;
    print end_form(),end_html();
}
elsif ($op eq 'preview') 
{  # this routine is not secured
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - $folder",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print start_multipart_form(-action=>$cgi_url, -method=>"post");
  print_title();
  print_folder();
  my $messagename = param('messagename') || " ";
  my $contents = param('contents') || " ";
  $contents =~ s/\n/<br>/gm if $contents;
  my $URI = param('URI') || "";
  my $linkname = param('linkname') || "";
  my $parentid = param('parent') || $folderid;
  $expand = param('expand') || 1;
  $folder =~ s/\ /\%20/g;
  print br;
  if( $name ) 
  {
    print "Welcome back, $name",br;
  }
  else 
  {
    print qq{<font color="FF0000"><b>Your entry will be added as "Guest" unless you }.
          qq{<a href="$cgi_url?op=login">login</a></b></font>},br;
  }
  print br;
  print qq{<table border=0><tr><td bgcolor="000000"><b>}.
        qq{<font color="cccccc" face="arial, helvetica,sans-serif">Message Preview</font>}.
        qq{</b></td></tr></table>\n};
  print "<table border=1><tr><td>";
  print "<font color=777777>" if ( $our_userid < 1 );
  print qq{<p class="body_text" }.
        qq{style="background-color: $fresh_title_background; border-style: outset; ">}.
        qq{<b>$messagename</b></p>}.
        qq{\n};
  print qq{<p class="body_text" }.
        qq{style="background-color: $fresh_message_background; ">};

  print qq{<font style="font-size=80%; "><b>};
  print qq{by $name};
  print qq{</b> - posted: datetime</font>},br;
  print_contents($contents);
  if ($URI) 
  {
    print qq{ [ <a href="$URI" target="_blank">};
    $linkname ? print "$linkname" : print "$URI";
    print "</a> ]";
  }
  print "</font></p>",br if ( $our_userid < 1 );
  print "</td></tr></table>";
  print br;
  $messagename =~ s/\\/\\\\/g;
  $messagename =~ s/"/\\"/g;
  $contents =~ s/\\/\\\\/g;
  $contents =~ s/"/\\"/g;
  $URI =~ s/\\/\\\\/g;
  $URI =~ s/"/\\"/g;
  print qq{<table border=0><tr><td bgcolor="000000"><b>}.
        qq{<font color="cccccc" face="arial, helvetica,sans-serif">Message Edit</font>}.
        qq{</b></td></tr></table>\n};
  print "<table border=1><tr><td>";
  print "Entry name: ",textfield(-name=>"messagename", -value=>"$messagename"),br;
  print "Message:",br,
        textarea(-name=>"contents", -value=> "$contents", -rows=>"12",
                 -columns=>"80", -wrap=>"virtual"),br;
  print "Is there a URL that goes with this message? ",
        textfield(-name=>"URI", -value=>"$URI", size=>50),br;
  print "&nbsp;&nbsp;&nbsp;&nbsp;Link name for the URL? ",
        textfield(-name=>"linkname",-value=>"$linkname",size=>50),br;
  my $clean_folder = $folder;
  $clean_folder =~ s/\%20/\ /g;
  print qq{<input type=hidden name="folder" value=\"$clean_folder\">};
  print qq{<input type=hidden name="parent" value="$parentid">};
  print qq{<input type=hidden name="folderid" value="$folderid">};
    print qq{<input type="hidden" name="expand" value="$expand">};
  print submit(-name=>'op', -value=>"preview");
  print submit(-name=>'op', -value=>"add entry");
  print "</td></tr></table>";
  print end_form(),end_html();
}
elsif ($op eq 'add entry' && ( (($otherw eq 'Y' || $our_userid == $ownerid)
      || ($groupmember eq 'Y' && $groupw eq 'Y')) && $open eq 'Y') ) 
{
  my $messagename = param('messagename') || "";

# If no subject has been inserted.  Stop.  Give warning.  Give instructions
  if ( $messagename eq '' )
  {
    print header(-cookie=>[$cookie]);
    print start_html(-title=>"$title_tag - Entry Error, No Subject",
                     -bgcolor=>"$page_background",
                     -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                     -style=>{-src=>'/re_default.css'});
    print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
    print_title();
    print qq{<img src="/icons/crystal/64x64/apps/error.png" width=64 }.
          qq{height=64 border=0 alt="" align="left">};
    print qq{<font style="font-size: 170%; font-weight: bold; font-color: #AA0000; ">}.
          qq{Subject line is required.  Use your browser's "back" button to }.
          qq{return to the previous page.</font>};
    print end_html();
    exit(0);
  }
  $messagename =~ s/\\/\\\\/g;
  $messagename =~ s/"/\\"/g;

  my $contents = param('contents') || "";
# If no body has been inserted.  Stop.  Give warning.  Give instructions
  if ( $contents eq '' )
  {
    print header(-cookie=>[$cookie]);
    print start_html(-title=>"$title_tag - Entry Error, No Body",
                     -bgcolor=>"$page_background",
                     -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                     -style=>{-src=>'/re_default.css'});
    print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
    print_title();
    print qq{<img src="/icons/crystal/64x64/apps/error.png" width=64 }.
          qq{height=64 border=0 alt="" align="left">};
    print qq{<font style="font-size: 170%; font-weight: bold; font-color: #AA0000; ">}.
          qq{Posting of empty messages is not allowed.  Use your browser's "back" button to }.
          qq{return to the previous page.</font>};
    print end_html();
    exit(0);
  }

  $contents =~ s/\\/\\\\/g;
  $contents =~ s/"/\\"/g;
  my $URI = param('URI') || "";
  $URI =~ s/\\/\\\\/g;
  $URI =~ s/"/\\"/g;
  $URI = '' if $URI eq 'http://';
  my $linkname = param('linkname') || "";
  $linkname =~ s/\\/\\\\/g;
  $linkname =~ s/"/\\"/g;
  my $parentid = param('parent') || $folderid;
  my $folderid = param('folderid');
  my $filename = param('upload_file') || ''; # grab name of upload file
  my $filename_mangle = $filename;
  $filename_mangle =~ s/\\/\//g;
  my @filename = split (/\//, $filename_mangle);
  my $filename_index = (@filename)-1;
  my $filename_store = $filename[$filename_index];
  $expand = param('expand');
  # print header(-cookie=>[$cookie]), "folder=$folder";
  $query = qq{INSERT into message 
              set 
              folderid = $folderid, 
              name="$messagename", 
              contents="$contents", 
              URI="$URI", 
              linkname="$linkname", 
              userid=$our_userid, 
              time=date_format(now(), '%Y%m%d%H%i%s'), 
              parentid=$parentid};
  # print header(-cookie=>[$cookie]), $query;
  $sth = $dbh->prepare ($query);
  $sth->execute ();

# update the thread table
    my $thread_root_message_id;
    if ($folderid == $parentid) # if this is a new thread, grab the id
    {
      # grab the message id of last message inserted.
      $query = qq{SELECT LAST_INSERT_ID() FROM message};
      $sth = $dbh->prepare ($query);
      $sth->execute ();
      my ($message_id) = $sth->fetchrow_array ();
      $thread_root_message_id = $message_id;
    }
    else # otherwise, backtrack for the thread root
    {
      my $hold_parentid = $parentid;
      {
        $query = qq{SELECT parentid, folderid FROM message WHERE id=$hold_parentid};
        $sth = $dbh->prepare ($query);
        $sth->execute ();
        my ($temp_parentid, $folderid) = $sth->fetchrow_array ();
        $thread_root_message_id = $hold_parentid;
        last if $temp_parentid == $folderid;
        $hold_parentid = $temp_parentid;
        redo;
      }
    }
    $query = qq{REPLACE threads SET id=$thread_root_message_id, time=now()};
    $sth = $dbh->prepare ($query);
    $sth->execute ();

    
# update the counter value in the user table
    $query = qq{SELECT user.id
                FROM user, members, message
                WHERE message.id=$folderid AND user.id=members.userid AND message.groupid=members.groupid};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    while ( @ary = $sth->fetchrow_array () ) {
      my ($folder_user_id) = @ary;
      my $query = qq{UPDATE $user_table
                      SET counter=counter+1, timestamp=timestamp #increment counter while freezing timestamp
                      WHERE id=$folder_user_id
                      };
      my $sth = $dbh->prepare ($query);
      $sth->execute ();
    }

  $sth->finish ();

  # grab the message id of last message inserted.
  $query = qq{SELECT LAST_INSERT_ID() FROM message};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($message_id) = $sth->fetchrow_array ();
  $sth->finish ();

  if ($filename) {
    $query = qq{SELECT id FROM attachment order by id desc limit 1};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    @ary = $sth->fetchrow_array ();
    my ($last_file_id) = @ary;
    $sth->finish ();
    my $this_file_id = $last_file_id + 1;  # incriment fileid by one (this is the new file name)
    my $local_file_name = sprintf("0%010d", $this_file_id);
    open (OUTFILE, ">$home_dir/re_files/$local_file_name")
          || die "can't write to $home_dir/re_files/$local_file_name\n$!\n";
    while (<$filename>) {
      print OUTFILE;
      }
    close(OUTFILE);
    $query = qq{INSERT into attachment SET
                filename = "$filename_store",
                date = date_format(now(), '%Y%m%d%H%i%s'),
                message_id = $message_id}; # last insert id
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    $sth->finish ();
    }
  $folder =~ s/\ /\%20/g;
  my $messagetag = ($folderid != $parentid) ? "#$parentid" : "#$message_id";
  update_folder_session($folderid);
  print redirect(-cookie=>[$cookie],
                 -location=>"$cgi_url?folder=$folder&expand=$expand$messagetag");
}
elsif ($op eq 'edit' && $open eq 'Y' ) 
{
  my $id = param('id') || '';
  $expand = param('expand') || 1;
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Edit Entry",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  $query = qq{SELECT userid, name, contents, URI, ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(time) ) as age
              FROM message
              WHERE id=$id};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  @ary = $sth->fetchrow_array ();
  my ($message_ownerid, $message_name, $message_contents, $message_URI, $message_age) = @ary;
  $sth->finish ();
  print_title(); 
  print start_form(-action=>"$cgi_url", -method=>"post");
  print <<"  END_OF_HTML";
  <center><h1>Edit Entry</h1></center>
  END_OF_HTML
  unless ($our_userid == $message_ownerid || $our_userid == 1) 
  {
    print "You cannot edit something that is not yours.",br;
  }
  elsif ( $message_age < $edit_interval )
  {
    my $preview_contents = $message_contents;
    # $preview_contents =~ s/\n/<br>/gm if $preview_contents;
    $folder =~ s/\ /\%20/g if $folder;
    print "<table border=1><tr><td>";
    print qq{<table border=0 cellpadding=1><tr>}.
          qq{<td bgcolor="$fresh_title_background">}.
          qq{<p class="body_text" style="border-style:outset; ">}.
          qq{<b>$message_name</b></p></td></tr>};
    print qq{<tr><td bgcolor="$fresh_message_background">};
    print qq{<p class="body_text">};
    print qq{<a href="$URI">link</a> - } if $URI;
    print_contents($preview_contents);
    print qq{</font>} if ( $our_userid < 1 );
    print qq{</p></tr></td></table>\n};
    print "</td></tr></table>";
    print "<table border=1><tr><td>";
    print "Entry name: ",textfield(-name=>"messagename", -value=>"$message_name"),br;
    print "Message:",br,
          textarea(-name=>"contents",
                   -value=> "$message_contents",
                   -rows=>"15",
                   -columns=>"80",
                   -wrap=>"virtual"),br;
    print "Is there a URL that goes with this message? ",
          textfield(-name=>"URI", -value=>"$message_URI"),br;
    my $clean_folder = $folder;
    $clean_folder =~ s/\%20/\ /g;
    print "<input type=hidden name=folder value=\"$clean_folder\">";
    print "<input type=hidden name=id value=$id>";
    print "<input type=hidden name=expand value=$expand>";
    # print submit(-name=>'op', -value=>"preview edit");
    print submit(-name=>'op', -value=>"save changes");
    print "</td></tr></table>";
    print end_form();
  }
  else
  {
    print qq{I'm sorry, this message is older than the edit interval};
  }
  print end_html();
}
elsif ($op eq 'save changes') {
  my $id = param('id');
  my $messagename = param('messagename') || " ";
#  $messagename =~ s#</?[^allow_HTML_tags].*?>##gmi;
  $messagename =~ s/\\/\\\\/g;
  $messagename =~ s/"/\\"/g;
  my $contents = param('contents') || " ";
#  $contents =~ s#</?[^allow_HTML_tags].*?>##gmi;
  $contents =~ s/\\/\\\\/g;
  $contents =~ s/"/\\"/g;
  my $URI = param('URI') || "";
#  $URI =~ s#</?[^allow_HTML_tags].*?>##gmi;
  $URI =~ s/\\/\\\\/g;
  $URI =~ s/"/\\"/g;
  my $parentid = param('parent') || $folderid;
  $expand = param('expand') || 1;
  $query = qq{UPDATE message
              SET
              name = "$messagename",
              contents = "$contents",
              URI = "$URI"
              WHERE id=$id};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  $sth->finish ();
  $folder =~ s/\ /\%20/g;
  print redirect(-cookie=>[$cookie],
                 -location=>"$cgi_url?folder=$folder&expand=$expand#$id");
  }
elsif ( $op eq 'create new account') {
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - create new account",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
  print qq{<p class="body_text" style="margin-top:0; margin-bottom:0; ">};
  print_title(); 
  print start_form(-action=>"$cgi_url", -method=>"post");
  print qq{<p class="body_text" style="margin-top:0; margin-bottom:0; ">};
  print "<table border=0>";
  print "<tr><td colspan=3>";
  print <<"  END_OF_HTML";
  <center><p class="body_text"><span style="font-size: 200%; ">
  Create New Account
  </span></center>
  <br>
  <p class="body_text">Your e-mail address and username will be checked for uniqueness.
  If no other users exist with the e-mail address or username you request,
  an e-mail will be sent to the e-mail address you provide. It will
  contain a computer generated, temporary password. You can log in with
  this temporary password. After you have logged in, you will be able to
  change your password.<br><br>
  END_OF_HTML
  print qq{</td></tr>};
  print qq{<tr><td align=right>login name:</td><td>},
        textfield(-name=>"user_name_app"),
        qq{</td></tr>};
  print qq{<tr><td valign=top align=right>email:</td>},
        qq{<td valign=top >},textfield(-name=>"email_app").
        qq{</td><td>}.
        qq{<span style="font-size: 90%; color: #aa0000; ">}.
        qq{<b>*</b>you <b>must</b> enter a valid e-mail address for confirmation }.
        qq{to be delivered.</font></td></tr>};
  print qq{<tr><td valign=top align=right>retype email:</td>},
        qq{<td valign=top >},textfield(-name=>"email_app1").
        qq{</td><td><br><br></td></tr>};
  print qq{<tr><td valign=top align=right>password:</td>},
        qq{<td valign=top >},password_field(-name=>"password_app").
        qq{</td><td>}.
        qq{<span style="font-size: 90%; color: #aa0000; ">}.
        qq{<b>*</b>you <b>must</b> enter a password.</font></td></tr>};
  print qq{<tr><td valign=top align=right>retype password:</td>},
        qq{<td valign=top >},password_field(-name=>"password_app1").
        qq{</td><td>}.
        qq{</td></tr>};
  print qq{<tr><td colspan=3><i>}.
        qq{Please verify that your e-mail is your valid, correct, and current e-mail <br>}.
        qq{address in the form of <b>username\@domain.com</b>. if your e-mail address <br>}.
        qq{is not correct, your account information cannot be delivered.}.
        qq{<i></p></td></tr>};
  print "</table>";
  print qq{<table border=1 bgcolor="$fresh_message_background" }.
        qq{width="80%"><td>$privacy_statement</td></table>};
  print submit(-name=>"op", -value=>"request account");
  print end_form(),end_html();
  }
elsif ( $op eq 'request account') 
{
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - create new account",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
  print_title(); 
  print "<center><h1>Create New Account</h1></center>";
  print "<font color=FF0000><b>";
  print "username required. use the back button to return to the ".
        "form and enter a username.",br,br
          unless my $user_name_app = param('user_name_app');
  print "Username cannot begin with '!'. use the back button to return to the ".
        "form and enter a username.",br,br
          if $user_name_app =~ /^!/;
  print "e-mail address required. Use the back button to ".
        "return to the form and enter a e-mail address.",br,br
          unless my $email_app = param('email_app');
  print "e-mail address confirmation is required. Use the back button to ".
        "return to the form and enter your e-mail address twice.",br,br
          unless my $email_app1 = param('email_app1');
  print "Your e-mail and confirmation did not match.  Use the back button to return to the form.",br
          unless $email_app eq $email_app1;
  print "Your password is required. Use the back button to ".
        "return to the form and enter a password.",br,br
          unless my $password_app = param('password_app');
  print "Password confirmation is required. Use the back button to ".
        "return to the form and enter a password.",br,br
          unless my $password_app1 = param('password_app1');
  print "Your passwords did not match.  Use the back button to return to the form.",br
          unless $password_app eq $password_app1;

# Error out if username or email are undefined or either password OR emails or passwords don't match
  if ($user_name_app eq '' or $user_name_app =~ /^\!/
     or $email_app eq '' or $email_app1 eq '' 
     or $password_app eq '' or $password_app1 eq ''
     or $email_app ne $email_app1
     or $password_app ne $password_app1 )
  {
    exit(0);
  }

  # Check for existing user name
  $query = qq{SELECT id FROM user WHERE name="$user_name_app" or  name="\!$user_name_app"};
  $sth = $dbh->prepare ($query);
  my $rv = int ( $sth->execute() );
  print "That username ($user_name_app) is already in use by another ".
        "user.  Please go back and try a different user name.",br
          if $rv > 0;
  my $stop_flag = 1 if $rv>0;

  # Check for existing e-mail address
  $query = qq{SELECT id FROM user WHERE email="$email_app"};
  $sth = $dbh->prepare ($query);
  $rv = int ( $sth->execute() );
  print "That e-mail address ($email_app) is already in use by another user. ".
        "If you already have an account with us, you can request it from the ".
        "user login page.",br if $rv > 0;
  $stop_flag = 1 if $rv>0;
  print "</b></font>";

  unless ( $stop_flag )  # unless $stop_flag is defined, proceed with user creation
  {

    # calculate md5 hash of new password
    my $pass_md5 = md5_hex($password_app);

# get a passkey for the user....
    my $passkey = passkey();

    # insert new user into user table
    my $query = qq{INSERT into user set name="!$user_name_app", email="$email_app",
                password="$pass_md5", passkey="$passkey"};
    my $sth = $dbh->prepare ($query);
    $sth->execute ();

    send_confirm_message("$user_name_app");

    # Print confirmation
    print "Your user account has been created.",br,br;
    print "You should be receiving an e-mail confirmation in the next few minutes. ",br,br;
    print "You may not have full access to the message base until the administrator ".
          "has added your account to the appropriate groups.",br,br;
    print qq{[ <a href="$cgi_url">Back to <b><i>$title_tag</i></b></a> ]};
  }
  $sth->finish ();
}
elsif ( $op eq 'resend confirmation' && $name eq 'root') # resend the confimration link (root only)
{
  my ($user_name_app) = param('user_name_app');
  send_confirm_message($user_name_app);
  print redirect(-cookie=>[$cookie], -location=>"$cgi_url?op=user%20administration");
}
elsif ( $op eq 'confirm') 
{
  my $secret = param('secret');
  my $user_name= param('user');
  print header();
  print start_html(-title=>"$title_tag - new account confirmation",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
  print_title(); 
  print "<center><h1>New Account Confirmation</h1></center>";
  my $query = qq{SELECT passkey, id FROM user WHERE name="!$user_name"};
  my $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($passkey, $id) = $sth->fetchrow_array ();

  if ( $passkey eq $secret )
  { # the secret matches the passkey, change user to confirmed
    $query = qq{UPDATE user SET name="$user_name", timestamp=timestamp WHERE id=$id};
    $sth = $dbh->prepare ($query);
    $sth->execute ();

    $query = qq{SELECT email FROM user WHERE id=$id};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    my ($email_app) = $sth->fetchrow_array ();

    print qq{Okay, everything is ready.  <a href="$cgi_url?op=login">Login</a>};

    # Send new account notification to site owner if requested
    if ($notification_address)
    {

    my $temp_domain_name = $domain_name;
    $temp_domain_name =~ s/^\.//; # strip leading '.' from beginning of domain name
                                  # if present.

    my $message = <<END_OF_MESSAGE;
$title_tag has a new user: $user_name ($email_app).
$notification_message

http://$temp_domain_name$cgi_url?op=manage%20this%20user&manage_userid=$id

Do not reply to this message.
END_OF_MESSAGE

      my $mailer = Mail::Mailer->new();
      $mailer->open({'From' => "$notification_address",
                    'To' => "$notification_address",
                    'Subject' => "$title_tag -- new user"} );
      print $mailer "$message";
      close($mailer);
    }

  }
  else
  {
    print qq{Something went wrong with the confirmation, please contact }.
          qq{<a href="mailto:$notification_address">$notification_address</a> if you continue to have}.
          qq{ problems.},br,br;
  }
  $sth->finish ();
}
elsif ( $op eq 'mail password') {
  my $email = param('email');
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - mail password",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print_title(); 
  print "<center><h1>Sending Password</h1></center>";

  # insert new user into user table
  $query = qq{SELECT name, password FROM user WHERE email="$email" AND id>1};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($name, $password) = $sth->fetchrow_array ();

  if ($password eq 'deactivate') {
    print "This account has been deactivated. Contact <b><i>$title_tag</i></b> ".
          "Administrator.\n";
    $sth->finish ();
    $dbh->disconnect ();
    exit(0);
    }
  else {
    # generate password
    my $dictionary = '/usr/share/dict/words';
    my $word1 = int (rand(45000));
    my $word2 = int (rand(45000));

    open (DICT, $dictionary) || warn ("!!! No dictionary found at $dictionary for password generation !!!\n");
    my $trash;
    for ($i=0; $i<$word1; $i++) {
      $trash = <DICT>;
      }
    $word1 = <DICT>;
    chomp($word1);
    close (DICT);
    open (DICT, $dictionary);
    for ($i=0; $i<$word2; $i++) {
      $trash = <DICT>;
      }
    $word2 = <DICT>;
    chomp($word2);
    close (DICT);

    my @chars = ( "+", "-", "=", "*", "#", "&" );
    my $joiner = @chars[ map { rand @chars } (1) ];

    $password = $word1.$joiner.$word2;

    # insert md5 hash of new password into user table
    my $pass_md5 = md5_hex($password);
    $query = qq{UPDATE user SET password='$pass_md5', timestamp=timestamp WHERE name="$name"};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    }

  if ($password) {
    my $temp_domain_name = $domain_name;
    $temp_domain_name =~ s/^\.//; # strip leading '.' from beginning of domain name
                                  # if present.

    my $message = <<END_OF_MESSAGE;
Your $title_tag login is $name.
Your password is '$password'.

The password is case sensitive, and does not include the quotes ('). 
After you have successfully logged into the message base, you can
change your password by clicking on the 'account maintenance' button, 
if you would prefer a different password.
 
Do not reply to this message.

http://$temp_domain_name
END_OF_MESSAGE

    my $mailer = Mail::Mailer->new();
    $mailer->open({'From' => "$notification_address",
                   'To' => "$email",
                   'Subject' => "Your $title_tag login"} );
    print $mailer "$message";
    close($mailer);

    print "A new password has been generated and sent to your e-mail.",br;
    print "After you have received your account information, you can ".
          "<a href=\"$cgi_url?op=login\">login</a>",br;
  }
  else
  {
    print "We do not have an account belonging to that e-mail address...",br;
    }
  }
elsif ( $op eq 'account maintenance' && $name ) {
  $expand = param('expand') || $thread_style;
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - User Maintenance",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print_title();
  print "<center><h1>Account Maintenance - $name</h1></center>";
  print start_form(-action=>$cgi_url, -method=>"post");
  if( $name ) {
    print "Welcome back, $name &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
    print submit(-name=>"op", -value=>"logout"),br;
    }
  else {
    print "<a href=\"$cgi_url?op=login\">login</a>",br;
    }
  print "Back to: <a href=$cgi_url?folder=$folder><b>$folder</b></a>",br,br;

  print "<font size=+1><b>Change Password for '$name':</b></font>",br;
  print "<table cellpadding=0 cellspacing=0>";
  print "<tr><td align=right>new password:</td><td>",
        password_field(-name=>"pass1", -value=>''),"</td></tr>";
  print "<tr><td align=right>retype password:</td><td>",
        password_field(-name=>"pass2", -value=>''),"</td></tr>";
  print "<tr><td align=right></td><td>",
        submit(-name=>"op", -value=>"change password"),"</td></tr>";
  print "</table>",br,br;

  my $query = qq{SELECT email
  FROM user
  WHERE id = $our_userid
  };
  my $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($user_email) = $sth->fetchrow_array () || 0;
  
  print "<font size=+1><b>Change e-mail address for '$name':</b></font>",br;
  print qq{[ Current email: <b>$user_email</b> ]},br;
  print "<table cellpadding=0 cellspacing=0>";
  print "<tr><td align=right>new e-mail address:</td><td>",
        textfield(-name=>"email1", -value=>''),"</td></tr>";
  print "<tr><td align=right>retype e-mail address:</td><td>",
        textfield(-name=>"email2", -value=>''),"</td></tr>";
  print "<tr><td align=right></td><td>",
        submit(-name=>"op", -value=>"change e-mail address"),"</td></tr>";
  print "</table>",br;
      
  $query = qq{SELECT thread
                  FROM user
                  WHERE id = $our_userid
                  };
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($user_thread) = $sth->fetchrow_array () || 0;
  print qq{<table>};
  print qq{<tr><td><b>Thread style:</b> </td><td>},
        radio_group(-name=>'thread_pref',
                        -values=>['0','1','2','3','4','5'],
                        -default=>"$user_thread",
                        ),
        qq{</td></tr>},
        qq{<tr><td></td><td>},
        qq{<font class="navigation" style="color: blue; ">},
        qq{<b>0</b> = "Accept site default" ($expand for this site);<br>},
        qq{<b>1</b> = "Expand all ``fresh'' messages and/or messages new since last visit";<br>},
        qq{<b>2</b> = "Expand threads, but only expand messages since last visit";<br>},
        qq{<b>3</b> = "Expand <u>every</u> message in <u>every</u> thread";},br,
        qq{<b>4</b> = "Only expand threads with new messages since last visit, and only expand },
        qq{new messages since last visit";},br,
        qq{<b>5</b> = "Expand all messages written in the last 7 days"},br,
        qq{</font>};
  print submit(-name=>"op", -value=>"set thread-style");
  print  qq{</td></tr></table>},br;

  print "<input type=hidden name='folder' value='$folder'>";
  print end_form(),end_html();
  }
elsif ( $op eq 'set thread-style' && $name ) {
  my $thread_pref = param('thread_pref') || 'NULL';
  $thread_pref = 0 unless ($thread_pref == 1 || $thread_pref == 2 || $thread_pref == 3 || $thread_pref == 4
           || $thread_pref == 5);
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Change Thread-style Preference",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print_title();
  print "Back to: <a href=$cgi_url?folder=$folder><b>$folder</b></a>",br;
  print "Back to: <a href=$cgi_url?folder=$folder&op=account%20maintenance><b>".
        "Account Maintenance</b></a>",br;
  $query = qq{UPDATE user SET thread='$thread_pref', timestamp=timestamp WHERE id=$our_userid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  print br,"Your prefered thread-style has been set to $thread_pref.",br;
  }
elsif ( $op eq 'change password' && $name ) {
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Change Password",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print_title();
  print "Back to: <a href=$cgi_url?folder=$folder><b>$folder</b></a>",br;
  print "Back to: <a href=$cgi_url?folder=$folder&op=account%20maintenance><b>".
        "Account Maintenance</b></a>",br;
  my $pass1 = param('pass1');
  my $pass2 = param('pass2');
  unless ($pass1 && $pass2) {
    print "You must enter your new password in both fields",br; 
    }
  elsif ($pass1 && ($pass1 eq $pass2) ) {
    unless ( $pass1 =~ /^[\w .-_?~]+$/ ) {
      print header(-cookie=>[$cookie]),"You used an illegal character in your password.";
      }
    else {
      my $pass_md5 = md5_hex($pass1);
      $query = qq{UPDATE user SET password='$pass_md5', timestamp=timestamp WHERE id=$our_userid};
      $sth = $dbh->prepare ($query);
      $sth->execute ();
      print br,"Your password has been updated.",br;
      }
    }
  else {
    print br,"Your passwords must match",br;
    }
  }
elsif ( $op eq 'change e-mail address') {
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Change E-mail Address",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print_title();
  print "Back to: <a href=$cgi_url?folder=$folder><b>$folder</b></a>",br;
  print "Back to: <a href=$cgi_url?folder=$folder&op=account%20maintenance><b>".
        "Account Maintenance</b></a>",br;
  my $email1 = param('email1');
  my $email2 = param('email2');
  unless ($email1 && $email2) {
    print "You must enter your new e-mail in both fields",br; 
    }
  elsif ($email1 && ($email1 eq $email2) ) {
    unless ( $email1 =~ /^[\w .-_?~@]+$/ ) {
      print header(-cookie=>[$cookie]),"You used an illegal character in your e-mail address.";
      }
    else {
      $query = qq{UPDATE user SET email='$email1', timestamp=timestamp WHERE id=$our_userid};
      $sth = $dbh->prepare ($query);
      $sth->execute ();
      print br,"Your e-mail has been updated.",br;
      }
    }
  else {
    print br,"Your e-mails must match",br;
    }
  }
elsif ( $op eq 'bury' && $name eq 'root') {
  my $messageid = param('id');
  my $root_message_id = param('root');
  my $confirm = param('confirm');
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - User Administration",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print_title(); 
  print qq{<center><h1>"Disapear" a message: $messageid</h1></center>};
  if ( $confirm ne 'Y' ) {
    print qq{<a href="$cgi_url?folder=$folder&op=expand&message=$root_message_id}.
          qq{#$root_message_id">},
          qq{Don't Delete -- Just go back</a>},br,br;
    # Grab the message info for the message to be displayed
    $query = qq{ SELECT message.name, contents, URI, linkname, user.name
                FROM message, user
                WHERE message.id=$messageid AND message.userid=user.id
                };
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    @ary = $sth->fetchrow_array ();
    my ($message_name, $message_contents, $URI, $linkname, $message_user) = @ary;
    $sth->finish ();
    $message_user = "Guest" unless $message_user;
    print "<table border=1><tr><td>";
      print "<table border=0 cellpadding=3 cellspacing=1>";
      print "<tr><td><b>$message_name</b></td></tr>";
      print "<tr><td>";
      print "<font size=-1><b>";
      print "by $message_user",br;
      print "</b></font>";
      print "<a href=$URI>link</a> - " if $URI;
      my $old_contents =~ s/\r/<br>/g;
      $folder =~ s/\ /%20/g;
      print "$message_contents",br;
      print "</td></tr></table>";
    print "</td></tr></table>";

    print qq{[ <a href="$cgi_url?folder=$folder&op=bury&id=$messageid&}.
          qq{root=$root_message_id&confirm=Y">CONFIRM DISAPEAR</a> ] },br;
    print qq{"Disapearing" a message will mark the message as "closed" (hidden) in the }.
          qq{database, and it will be ignored when messages are threaded. The message }.
          qq{will remain in the database. At this time, the only way to bring the message }.
          qq{back is to go into the database and do it manually.},br;
    }
  else {
    print qq {Message $messageid "Disapeared."},br;
    print qq{<a href="$cgi_url?folder=$folder&op=expand&message=$root_message_id}.
          qq{#$root_message_id">Go back</a>},br,br;
    # Update record to set message as "closed" (hidden)
    $query = qq{UPDATE message SET open='N' WHERE id=$messageid};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    $sth->finish ();
    }
  print end_html;
  }
elsif ( $op eq 'user administration' && $name eq 'root') {
  my $sort_order = param('sort') || 'A'; # get sort by key, or 'name' by default
  my $filter = param('filter') || 'A'; # get a filter type for account display filtering
  # $sort_order = param('sort_order');
  my $deactive_pass = md5_hex('deactivate'); # this is what a deactivated password looks like

  # sorting search results by means other than score
  my ($order, $order_description);
  if ( $sort_order eq "A" ) # sort by username, ASC
  {
    $order = "user.name ASC";
    $order_description = "sorted by username<br>";
  }
  elsif ( $sort_order eq "B" ) # sort by username, DESC
  {
    $order = "user.name DESC";
    $order_description = "sorted by username, descending<br>";
  }
  elsif ( $sort_order eq "C" ) # sort by last on date/time DESC
  {
    $order = "user.timestamp DESC";
    $order_description = "sorted by page access, most recent first<br>";
  }
  elsif ( $sort_order eq "D" ) # sort by last on date/time ASC
  {
    $order = "user.timestamp ASC";
    $order_description = "sorted by page access, oldest first<br>";
  }
  elsif ( $sort_order eq "E" ) # sort by last message post DESC
  {
    $order = "last_message DESC";
    $order_description = "sorted by last message posted -- most recent first<br>";
  }
  elsif ( $sort_order eq "F" ) # sort by last message post ASC
  {
    $order = "last_message ASC";
    $order_description = "sorted by last message posted -- oldest first<br>";
  }
  elsif ( $sort_order eq "G" ) # sort by number of posts DESC
  {
    $order = "messagecount DESC";
    $order_description = "sorted by number of posts, descending<br>";
  }
  elsif ( $sort_order eq "H" ) # sort by number of posts ASC
  {
    $order = "messagecount ASC";
    $order_description = "sorted by number of posts, ascending<br>";
  }
  else
  {
    $order = "user.name ASC";
    $order_description = "";
  }

  # set user filter up
  my ($sql_filter, $filter_description);
  if ( $filter eq "A" ) # show all user accounts (default)
  {
    $sql_filter = "";
    $filter_description = "all user accounts shown<br>";
  }
  elsif ( $filter eq "B" ) # show only active user accounts
  {
    $sql_filter = qq{AND user.password != "$deactive_pass"};
    $filter_description = "only active user accounts shown<br>";
  }
  elsif ( $filter eq "C" ) # show only deactivated user accounts
  {
    $sql_filter = qq{AND user.password = "$deactive_pass"};
    $filter_description = "only deactivated user accounts shown<br>";
  }
  else # default to "A"
  {
    $filter = 'A';
    $sql_filter = "";
    $filter_description = "all user accounts shown<br>";
  }

  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - User Administration",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print_title(); 
  print "<center><h1>User Administration</h1></center>";

  $query = qq{SELECT count(*) from user where password="$deactive_pass"};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($deactive_user_count) = @ary = $sth->fetchrow_array ();

  $query = qq{SELECT count(*) from user where password!="$deactive_pass"};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($active_user_count) = @ary = $sth->fetchrow_array ();

  $query = qq{SELECT user.id, user.name, user.email, 
                     date_format(user.timestamp, '%d.%b.%Y %H:%i'),
                     date_format(MAX(message.time), '%d.%b.%Y %H:%i'),
                     count(message.id) as messagecount,
                     user.password,
                     MAX(message.time) as last_message
              FROM user
                LEFT JOIN message ON user.id=message.userid AND message.folder='N'
              WHERE user.id > 1
                    # AND message.folder='N'
                    $sql_filter
              GROUP BY user.id
              ORDER BY $order
              };
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    my ($manage_userid, $manage_username, @manage_userid,
        %manage_usernames, $manage_email, $user_pass);
    # print table with users sorted by username
    print qq{[ <a href="$cgi_url">Back to <b><i>$title_tag</i></b></a> ]},br,br;
    print "Users: <i>$order_description</i>";

    # user filtering options
    print qq{<font style="font-size: 80%; ">User account filter options: <b>};
    print qq{[ <a href="$cgi_url?op=user%20administration&sort=$sort_order&filter=A">}.
          qq{Show all users</a> ]} unless $filter eq 'A';
    print qq{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} unless $filter eq 'A';
    print qq{[ <a href="$cgi_url?op=user%20administration&sort=$sort_order&filter=B">}.
          qq{Show only active users</a> ]} unless $filter eq 'B';
    print qq{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} unless $filter eq 'B';
    print qq{[ <a href="$cgi_url?op=user%20administration&sort=$sort_order&filter=C">}.
          qq{Show only deactivated users</a> ]} unless $filter eq 'C';
    print qq{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;};
    print qq{[ Users: $active_user_count Active/<font style="color: #AA0000; ">$deactive_user_count }.
          qq{Inactive</font> ]};
    print qq{</b></font>};

    # create the user sumary table
    print qq{<table border=1 style="font-size: 85%; ">};
    print qq{<tr bgcolor="$fresh_title_background">};
    print qq{<th align="center">username }.
          qq{<font style="font-size: 60%; ">}.
          qq{(<a href="$cgi_url?op=user%20administration&sort=A&filter=$filter">A</a>}.
          qq{/}.
          qq{<a href="$cgi_url?op=user%20administration&sort=B&filter=$filter">D</a>)}.
          qq{</font></th>};
    print qq{<th align="center">e-mail</th>};
    print qq{<th align="center">posts }.
          qq{<font style="font-size: 60%; ">}.
          qq{(<a href="$cgi_url?op=user%20administration&sort=H&filter=$filter">A</a>}.
          qq{/}.
          qq{<a href="$cgi_url?op=user%20administration&sort=G&filter=$filter">D</a>)}.
          qq{</font></th>};
    print qq{<th align="center">last post }.
          qq{<font style="font-size: 60%; ">}.
          qq{(<a href="$cgi_url?op=user%20administration&sort=F&filter=$filter">A</a>}.
          qq{/}.
          qq{<a href="$cgi_url?op=user%20administration&sort=E&filter=$filter">D</a>)}.
          qq{</font></th>};
    print qq{<th align="center">last access }.
          qq{<font style="font-size: 60%; ">}.
          qq{(<a href="$cgi_url?op=user%20administration&sort=D&filter=$filter">A</a>}.
          qq{/}.
          qq{<a href="$cgi_url?op=user%20administration&sort=C&filter=$filter">D</a>)}.
          qq{</font></th>};
    print qq{<th align="center">status</th>};
    print qq{<th align="center">grp</th>};
    print qq{</tr>};
    my $alternate_line_color = 'off'; # turn off "green bar" for first line
    while ( @ary = $sth->fetchrow_array () ) 
    {
      my ($manage_userid, $manage_username, $manage_email, $last_access_time, 
        $last_message_time, $message_count, $user_pass) = @ary;
      $last_message_time = "<center>-</center>" unless $last_message_time;
      $last_access_time = "<center>-</center>" unless $last_access_time;

      # "green bar"
      if ($alternate_line_color eq "on") 
      {
        print qq{<tr bgcolor="$fresh_title_background">}; # start table row
        $alternate_line_color = 'off'; # turn off "green bar" for next line
      }
      else 
      {
        print qq{<tr bgcolor="$fresh_message_background">}; # start table row
        $alternate_line_color = 'on'; # turn on "green bar" for next line
      }

      print qq{<td><a href="$cgi_url?op=manage%20this%20user&manage_userid=$manage_userid" }.
            qq{title="manage this user: $manage_username">}.
            qq{$manage_username</a></td>}; # print username
      print qq{<td><a href="mailto:$manage_email" }.
            qq{title="e-mail this user: $manage_username"}.
            qq{>$manage_email</a></td>}; # print username
      print qq{<td>$message_count</td>};
      print qq{<td>$last_message_time</td>};
      print qq{<td>$last_access_time</td>};
      if ( $user_pass eq $deactive_pass )
      {
         print qq{<td><font color="#AA0000">Deactivated</font></td>}
      }
      elsif ( $manage_username =~ /^\!/ )
      {
         $manage_username =~ s/^\!//;
         print qq{<td><a href="$cgi_url?op=resend%20confirmation&user_name_app=$manage_username">resend conf</a></td>};
      }
      else
      {
         print qq{<td>Active</td>};
      }

      my $query = qq{SELECT count(members.groupid)
                  FROM members
                  WHERE members.userid = $manage_userid
                  GROUP BY userid
                  };
      my $sth = $dbh->prepare ($query);
      $sth->execute ();
      my ($group_count) = $sth->fetchrow_array () || 0;
      print qq{<td>$group_count</td>};
      print qq{</tr>}; # end table row
    }
  print qq{</table>}; # end table
  $sth->finish ();

  print br,qq{[ <a href="$cgi_url?op=group%20administration">Jump to Group Administration</a> ]},br;
  print qq{ [ <a href="$cgi_url">Back to <b><i>$title_tag</i></b></a> ]};
  print end_form(), end_html();
  }
elsif ( $op eq 'manage this user' && $name eq 'root') {
  my $manage_userid = param('manage_userid');
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - User Administration",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print_title(); 

  # Snag the user's name
  $query = qq{SELECT name, email
              FROM $user_table 
              WHERE id=$manage_userid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($manage_username, $manage_email) = $sth->fetchrow_array ();

  print "<center><h1>User Administration -- $manage_username</h1></center>";

  print start_form(-action=>$cgi_url, -method=>"post");
  print qq{<b>User name: $manage_username }
        if $manage_userid != 1; # root's name can't change
  print submit(-name=>'op', -value=>'change user name'),br,br
        if $manage_userid != 1; # root's name can't change
  print qq{e-mail address:</b> };
  print textfield(-name=>'email', -size=>30, -value=>"$manage_email"),br,br;
  print "<table cellpadding=0 cellspacing=0>";
  print "<tr><td align=right>new password:</td><td>",
        password_field(-name=>"pass1", -value=>''),"</td></tr>";
  print "<tr><td align=right>retype password:</td><td>",
        password_field(-name=>"pass2", -value=>''),"</td></tr>";
  print "</table>",br;
  # Get a list of group names and ids
  $query = qq{SELECT id, name FROM groups ORDER BY name ASC};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my (@group_id, %group_names);
  while ( @ary = $sth->fetchrow_array () ) {
    my ($group_id, $group_name) = @ary;
    push (@group_id, $group_id);
    $group_names{$group_id} = $group_name;
    }

  # Get a count of messages posted by this user
  $query = qq{SELECT count(*) FROM message WHERE userid=$manage_userid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($message_count) =  @ary = $sth->fetchrow_array ();
  $sth->finish ();
  print qq{<b>Messages posted:</b> $message_count},br;

  # Get date/time of last message posted by user
  $query = qq{SELECT date_format(MAX(message.time), '%e.%b.%Y %h:%i %p') }.
           qq{FROM message WHERE userid=$manage_userid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($last_message_time) =  @ary = $sth->fetchrow_array () || '-';
  $sth->finish ();
  print qq{<b>Last message posted:</b> $last_message_time},br;

  # Get date/time of last page access by user
  $query = qq{SELECT date_format(timestamp, '%e.%b.%Y %h:%i %p') }.
           qq{FROM user WHERE id=$manage_userid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($last_access_time) = $sth->fetchrow_array ();
  $sth->finish ();
  print qq{<b>Last page access:</b> $last_access_time},br,br;

  # Get a list of groups this user is a member of
  $query = qq{SELECT groupid FROM members WHERE userid=$manage_userid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($member_groupid, @member_groups);
  while ( @ary = $sth->fetchrow_array () ) {
    ($member_groupid) = @ary;
    push (@member_groups, $member_groupid);
    }
  $sth->finish ();

  print qq{<input type="hidden" name="manage_userid" value="$manage_userid">};
  print qq{<input type="hidden" name="manage_username" value="$manage_username">};
  print "<b>Select groups for this user</b>",br;
  print checkbox_group(-name=>'groupid',
                       -values=>[@group_id],
                       -labels=>\%group_names,
                       -linebreak=>'true',
                       -default=>[@member_groups],
                       -columns=>3),br;

  print submit(-name=>'op', -value=>'update user'),br;

  # if user has not created any messages, they can safely be deleted
  if ($message_count == 0) {
    print "This user has not created any messages, and can safely be deleted.",br;
    print submit(-name=>"op",
                   -value=>"delete user")," ";
    print checkbox(-name=>"confirm delete",
                   -value=>"confirm delete"),br;
    }

  print br,qq{[ <a href="$cgi_url?op=user%20administration">Back to User Administration</a> ]},br;
  print qq{[ <a href="$cgi_url?op=group%20administration">Jump to Group Administration</a> ]},br;
  print qq{ [ <a href="$cgi_url">Back to <b><i>$title_tag</i></b></a> ]};
  print end_form(), end_html();
  $sth->finish ();
  }
elsif ( $op eq 'change user name' && $name eq 'root') {
  my $manage_userid = param('manage_userid');
  my $manage_username = param('manage_username');
  my $manage_email = param('email');
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Username Change",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print_title(); 

  if ( $manage_userid != 1 ) { # root's name can't change
    print "<center><h1>Change Username -- $manage_username</h1></center>";

    print start_form(-action=>$cgi_url, -method=>"post");
    print qq{<input type="hidden" name="manage_userid" value="$manage_userid">};
    print qq{<input type="hidden" name="manage_email" value="$manage_email">};
    print qq{username: };
    print textfield(-name=>'change_username', -size=>30, -value=>"$manage_username"),br,br;
    print qq{<b>If you change the username, the user must use }.
          qq{the new name when logging in from now on.</b>},br;
    print checkbox(-name=>"confirm username change",
                   -value=>"confirm username change"),br;
    print submit(-name=>"op",
                 -value=>"complete username change"),br;
    print end_form(), end_html();
    }
  else {
    print "<center><h1>Change Username -- Chaning root's name not allowed</h1></center>";
    }
  }
elsif ( $op eq 'complete username change' && $name eq 'root') {
  my $manage_userid = param('manage_userid');
  my $manage_username = param('manage_username');
  my $change_username = param('change_username');
  my $manage_email = param('manage_email');
  my $confirm = param('confirm username change');

  # first, we'll need to confirm new username not in use
  $query = qq{SELECT count(id) FROM user WHERE name="$change_username"};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($account_count) = $sth->fetchrow_array ();

  if ($manage_username eq 'root' ) {
    # return message that 'root' cannot be changed
    print header(-cookie=>[$cookie]);
    print start_html(-title=>"$title_tag - Username Change Not Allowed",
                     -bgcolor=>"$page_background",
                     -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                     -style=>{-src=>'/re_default.css'});
    print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
    print_title(); 

    print "<center><h1>Username Change Failed -- 'root' cannot be changed</h1></center>",br,br;
   }
  # if username already taken, return error
  elsif ($account_count > 0) {
    # return message that username in use
    print header(-cookie=>[$cookie]);
    print start_html(-title=>"$title_tag - Username Change Not Completed",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
    print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
    print_title(); 

    print qq{<center><h1>Username Change Failed -- $change_username already in }.
          qq{use</h1></center>},br,br;
    print qq{Use your 'back' button to go back and try another user name.},br,br;
    }

  elsif ( $confirm ne 'confirm username change'){
    # if "confirm" checkbox not checked, we can't change the username
    print header(-cookie=>[$cookie]);
    print start_html(-title=>"$title_tag - Username Change Not Completed",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
    print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
    print_title(); 

    print "<center><h1>Username Change Failed -- Confirmation Required</h1></center>",br,br;
    print qq{Use your 'back' button to go back. Check the "confirm username change" }.
          qq{checkbox to complete change.},br,br;
    }
  # otherwise...
  else {
    # update the user to the new name
    $query = qq{UPDATE user SET name="$change_username", timestamp=timestamp WHERE id=$manage_userid};
    $sth = $dbh->prepare ($query);
    $sth->execute ();

    # and return a username changed confirmation notice.
    print header(-cookie=>[$cookie]);
    print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
    print_title(); 

    print "<center><h1>Username Change Complete -- $change_username</h1></center>",br,br;
    print qq{username has been changed.},br,br;
    print qq{a notice of the username change has been sent to the e-mail address of }.
          qq{this user ($manage_email).},br,br;
    $sth->finish ();

    # send a note to the user notifying them that the change has been made by root.
    my $temp_domain_name = $domain_name;
    $temp_domain_name =~ s/^\.//; # strip leading '.' from beginning of domain name
                                  # if present.

    my $message = <<END_OF_MESSAGE;
Your $title_tag account username has been changed by the site administrator.


Your new account name is: $change_username

You will be required to use this username whenever logging from now on.

All of your old messages will now appear under this name. If there is
an error, or a correction needs to be made, please contact your 
RealizationEngine administrator.

http://$temp_domain_name
END_OF_MESSAGE

    my $mailer = Mail::Mailer->new();
    $mailer->open({'From' => "$notification_address",
                   'To' => "$manage_email",
                   'Subject' => "Your $title_tag account update"} );
    print $mailer "$message";
    close($mailer);
    }
  print qq{[ <a href="$cgi_url?op=user%20administration">Back to User Admin</a> ]},br;
  print qq{ [ <a href="$cgi_url">Back to <b><i>$title_tag</i></b></a> ]};
  }
elsif ( $op eq 'delete user' && $name eq 'root') {
  my $manage_userid = param('manage_userid');
  my $manage_username = param('manage_username');
  my $confirm_delete = param('confirm delete');

  # Get a count of how many messages this user owns
  $query = qq{SELECT count(id) FROM message WHERE userid=$manage_userid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($message_count) = $sth->fetchrow_array ();
  $sth->finish ();

  if ($message_count == 0 && $confirm_delete eq 'confirm delete') {
    # delete all instances of user from groups
    $query = qq{DELETE FROM members WHERE userid=$manage_userid};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    $sth->finish ();

    # delete user from user table
    $query = qq{DELETE FROM user WHERE id=$manage_userid};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    $sth->finish ();
    print redirect(-cookie=>[$cookie], -location=>"$cgi_url?op=user%20administration");
    }
  else {
    print header(-cookie=>[$cookie]);
    print start_html(-title=>"$title_tag - Delete user failed",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
    print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
    print_title(); 
    print "<center><h1>Failed to Delete user: $manage_username</h1></center>";
    print br,"Problem deleting user.",br;
    print "You must check the 'confirm delete' button to delete a user.",br,br
          if $confirm_delete ne 'confirm delete';

    print qq{[ <a href="$cgi_url?op=user%20administration">Back to User Admin</a> ]},br;
    }
  $sth->finish ();
  }
elsif ( $op eq 'update user' && $name eq 'root') {
  my $manage_userid = param('manage_userid');
  my $manage_username = param('manage_username');
  my $manage_email = param('email');
  my $pass1 = param('pass1');
  my $pass2 = param('pass2');
  my @user_groups = param('groupid');

  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - User Administration",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
  print_title(); 
  print "<center><h1>User Administration -- $manage_username</h1></center>";

  $query = qq{UPDATE user SET email="$manage_email", timestamp=timestamp WHERE id=$manage_userid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();

  $query = qq{DELETE FROM members WHERE userid=$manage_userid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  $sth->finish ();

  foreach $groupid (@user_groups) {
    $query = qq{INSERT INTO members VALUES ($groupid, $manage_userid)};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    $sth->finish ();
    }

  my ($manage_groupname, @manage_groupname);
  foreach $groupid (@user_groups) {
    $query = qq{SELECT name
                FROM groups 
                WHERE id=$groupid};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    while ( @ary = $sth->fetchrow_array () ) {
      ($manage_groupname) = @ary;
      push (@manage_groupname, $manage_groupname);
      }
    }

    my $new_group_names = join(', ', @manage_groupname);

  # send notification to user that groups have been changed
    my $temp_domain_name = $domain_name;
    $temp_domain_name =~ s/^\.//; # strip leading '.' from beginning of domain name
                                  # if present.

  open MAIL, "| $sendmail_path -i -t -f $notification_address";
  print MAIL <<END_OF_MESSAGE;
To: $manage_email
From: $notification_address
Subject: Your $title_tag account update
Your $title_tag account, $manage_username, has been updated.

You are now assigned to the following groups:
$new_group_names

http://$temp_domain_name
END_OF_MESSAGE

  if ($pass1) {
    unless ($pass1 && $pass2) {
      print "You must enter the new password in both fields",br; 
      }
    elsif ($pass1 && ($pass1 eq $pass2) ) {
      unless ( $pass1 =~ /^[\w .-_?~]+$/ ) {
        print header(-cookie=>[$cookie]),"You used an illegal character in the password.";
        }
      else {
        my $pass_md5 = md5_hex($pass1);
        $query = qq{UPDATE user SET password='$pass_md5', timestamp=timestamp WHERE id=$manage_userid};
        $sth = $dbh->prepare ($query);
        $sth->execute ();
        print br,"The password for $manage_username has been updated.",br;
        }
      }
    else {
      print br,"The passwords must match. No change has been made to the password.",br;
      }
    }

  print "Changes made to $manage_username.",br;
  print br;
  print qq{[ <a href="$cgi_url?op=user%20administration">Back to User Admin</a> ]},br;
  print qq{[ <a href="$cgi_url?op=group%20administration">Jump to Group Administration</a> ]},br;
  print qq{ [ <a href="$cgi_url">Back to <b><i>$title_tag</i></b></a> ]};
  $sth->finish ();
  }
elsif ( $op eq 'group administration' && $name eq 'root') {
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Group Administration",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
  print_title(); 
  print "<center><h1>Group Administration</h1></center>";

  # collect a list of groupnames and ids
  $query = qq{SELECT id, name
              FROM groups
              ORDER BY name};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    my ($manage_groupid, $manage_groupname, @manage_groupid, %manage_groupnames);
    while ( @ary = $sth->fetchrow_array () ) {
      ($manage_groupid, $manage_groupname) = @ary;
      push (@manage_groupid, $manage_groupid);
      $manage_groupnames{$manage_groupid} = $manage_groupname;
      }
  $sth->finish ();

  print start_form(-action=>$cgi_url, -method=>"post");
  print "<table><tr><td valign=top>";
  print scrolling_list(-name=>'manage_groupid',
                       -values=>[@manage_groupid],
                       -size=>15,
                       -multiple=>0,
                       -labels=>\%manage_groupnames),br;
  print submit(-name=>'op', -value=>'manage this group'),br,br;
  print "</td><td valign=top>";
  print "Create new group:",br;
  print textfield(-name=>'new group name', -value=>'', size=>20);
  print submit(-name=>'op', -value=>'create this group'),br;
  print "</td></tr></table>";
  print qq{ [ <a href="$cgi_url">Back to <b><i>$title_tag</i></b></a> ]};
  print qq{ [ <a href="$cgi_url?op=user%20administration">User Administration</a> ]},br;
  print end_form(), end_html();
  }
elsif ( $op eq 'create this group' && $name eq 'root') {
  print header(-cookie=>[$cookie]);
  my $new_group_name = param('new group name') || 'NULL';
  print start_html(-title=>"$title_tag - Group Administration - Create Group",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print_title(); 
  print "<center><h1>Group Administration</h1><b>Create Group</b></center>";

  if ($new_group_name eq 'NULL') { # if no name given, give error and link back.
    print "<b><font color=AA0000>no group name provided for new group</font></b>",br;
    print qq{[ <a href="$cgi_url?op=group%20administration">Back to Group Administration</a> ]},br;
    }
  else { # check sent name for uniqueness against existing groups
    # collect a list of groupnames and ids
    $query = qq{SELECT id
                FROM groups
                WHERE name = '$new_group_name'};
      $sth = $dbh->prepare ($query);
      my $rv = int ( $sth->execute() );
      if ($rv > 0) { # if name exists in groups table, kick out message and return link.
        print "<b>$new_group_name</b> already in use. Try another.",br;
        print qq{[ <a href="$cgi_url?op=group%20administration">Back to Group }.
          qq{Administration</a> ]},br;
        }
      else { # otherwise, create the new group and present link to edit group
        $query = qq{INSERT INTO groups
                    SET name='$new_group_name'};
        $sth = $dbh->prepare ($query);
        $sth->execute ();
        $query = qq{SELECT LAST_INSERT_ID() FROM groups};
        $sth = $dbh->prepare ($query);
        $sth->execute ();
        my ($new_group_id) = $sth->fetchrow_array ();
        print start_form(-action=>"$cgi_url", -method=>"post");
        print "New group: <b>$new_group_name</b> created successfully",br;
        print submit(-name=>"op", -value=>"manage this group");
        print hidden(-name=>"manage_groupid", value=>"$new_group_id"),br;
        print qq{ [ <a href="$cgi_url?op=group%20administration">Back to Group }.
              qq{Administration</a> ]},br;
        print qq{ [ <a href="$cgi_url?op=user%20administration">User Administration</a> ]},br;
        print qq{[ <a href="$cgi_url">Back to <b><i>$title_tag</i></b></a> ]},br;
        print end_form();
        }  
    $sth->finish ();
    }
  print end_html();
  }
elsif ( $op eq 'manage this group' && $name eq 'root') {
  my $manage_groupid = param('manage_groupid');
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Group Administration -- managing group",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
  print_title(); 

  # Snag the group's name
  $query = qq{SELECT name FROM groups WHERE id=$manage_groupid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($manage_groupname) = $sth->fetchrow_array ();

  print "<center><h1>Group Administration -- $manage_groupname</h1></center>";

  # Get a list of user names and ids
  $query = qq{SELECT id, name FROM $user_table ORDER BY name ASC};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my (@user_id, %user_names, $user_id, $user_name);
  while ( @ary = $sth->fetchrow_array () ) {
    ($user_id, $user_name) = @ary;
    push (@user_id, $user_id);
    $user_names{$user_id} = $user_name;
    }

  # Get a list of users this user is a member of
  $query = qq{SELECT userid FROM members WHERE groupid=$manage_groupid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my $rv = $sth->rows(); # number of users in group
  my ($member_userid, @member_users);
  while ( @ary = $sth->fetchrow_array () ) {
    ($member_userid) = @ary;
    push (@member_users, $member_userid);
    }
  $sth->finish ();

  print qq{<p class="body_text">};
  print qq{<font style="font-size: 75%; background-color: $fresh_title_background; padding: }.
        qq{3px; ">There are currently, $rv members in "<b>$manage_groupname</b>"</font>},br;
  print start_form(-action=>$cgi_url, -method=>"post");
  print qq{<p class="body_text">};
  print qq{<input type="hidden" name="manage_groupid" value="$manage_groupid">};
  print qq{<input type="hidden" name="manage_groupname" value="$manage_groupname">};
  print "Select users for this group",br;
  print checkbox_group(-name=>'userid',
                       -values=>[@user_id],
                       -labels=>\%user_names,
                       -linebreak=>'true',
                       -default=>[@member_users],
                       -columns=>5),br;

  print submit(-name=>'op', -value=>'update group')," ";
  print submit(-name=>'op', -value=>'delete group')," ";
  print checkbox(-name=>"confirm delete",
                 -value=>"confirm delete"),br,br;
  print qq{[ <a href="$cgi_url?op=group%20administration">Back to Group Administration</a> ]},br;
  print qq{[ <a href="$cgi_url?op=user%20administration">Jump to User Administration</a> ]},br;
  print qq{[ <a href="$cgi_url">Back to <b><i>$title_tag</i></b></a> ]};
  print end_form(), end_html();
  $sth->finish ();
  }
elsif ( $op eq 'update group' && $name eq 'root') {
  my $manage_groupid = param('manage_groupid');
  my $manage_groupname = param('manage_groupname');
  my @group_users = param('userid');

  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Group Administration -- update $manage_groupname",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print_title(); 
  print "<center><h1>Group Administration -- $manage_groupname</h1></center>";

  $query = qq{DELETE FROM members WHERE groupid=$manage_groupid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  $sth->finish ();

  foreach my $userid (@group_users) {
    $query = qq{INSERT INTO members VALUES ($manage_groupid, $userid)};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    $sth->finish ();
    }

  print "Changes made to $manage_groupname",br;
  print br;
  print qq{[ <a href="$cgi_url?op=group%20administration">Back to Group Admin</a> ]},br;
  print qq{[ <a href="$cgi_url?op=user%20administration">Jump to User Admin</a> ]},br;
  print qq{ [ <a href="$cgi_url">Back to <b><i>$title_tag</i></b></a> ]};
  $sth->finish ();
  }
elsif ( $op eq 'delete group' && $name eq 'root') 
{
  my $manage_groupid = param('manage_groupid');
  my $manage_groupname = param('manage_groupname');
  my $confirm = param('confirm delete');

  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Group Administration -- delete $manage_groupname",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  print_title(); 
  print "<center><h1>Group Administration -- DELETE $manage_groupname</h1></center>";

  if ($confirm eq 'confirm delete') 
  {
    $query = qq{DELETE FROM members WHERE groupid=$manage_groupid};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    $sth->finish ();

    $query = qq{DELETE FROM groups WHERE id=$manage_groupid};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    $sth->finish ();

    print "$manage_groupname deleted",br;
    print qq{<a href="$cgi_url?op=group%20administration">Back to Group Admin</a>},br;
  }
  else 
  {
    print qq{<font color="AA0000"><b>Delete refused.</b></font> You must check the }.
          qq{"confirm delete" in order to delete a group.},br;
    print qq{<a href="$cgi_url?op=manage%20this%20group&manage_groupid=$manage_groupid">}.
          qq{Back to manage $manage_groupname menu.</a>};
  }
}
elsif ( $op eq 'attachment administration' && $name eq 'root') 
{
  my $sort_order = param('sort') || "A";
  my $hide = param('hide') || "yes";

  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Attachment Administration",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
  print start_form(-action=>$cgi_url, -method=>"post");
  print qq{<p class="body_text" style="font-size:90%; ">};
  print_title(); 
  print qq{<p class="body_text" style="font-size:200%; font-weight: bold; }.
        qq{text-align: center; margin-top: 1em; margin-bottom: 1em; ">}.
        qq{Attachment Administration</p>\n};

  print qq{<p class="body_text" style="font-size:90%; ">};
  print qq{ [ <a href="$cgi_url">Back to <i>$title_tag</i></a> ]\n},br;
  my ($order, $order_description);
  unless ( -e  <$home_dir/re_files/archive.*>)  # if the archive file does not exist we will continue....
  {
    if ( $sort_order eq "A" ) # sort by id -- ASC
    {
      $order = "attachment.id ASC";
      $order_description = "date added -- ascending";
    }
    elsif ( $sort_order eq "B" ) # sort by id -- DESC
    {
      $order = "attachment.id DESC";
      $order_description = "date added -- descending";
    }
    elsif ( $sort_order eq "C" ) # sort by last access -- ASC
    {
      $order = "last_access ASC";
      $order_description = "last access -- ascending";
    }
    elsif ( $sort_order eq "D" ) # sort by last access -- DESC
    {
      $order = "last_access DESC";
      $order_description = "last access -- descending";
    }
    elsif ( $sort_order eq "E" ) # sort by username -- ASC
    {
      $order = "user.name ASC";
      $order_description = "username -- ascending";
    }
    elsif ( $sort_order eq "F" ) # sort by username -- DESC
    {
      $order = "user.name DESC";
      $order_description = "username -- descending";
    }
    elsif ( $sort_order eq "G" ) # sort by filename -- ASC
    {
      $order = "attachment.filename ASC";
      $order_description = "filename -- ascending";
    }
    elsif ( $sort_order eq "H" ) # sort by filename -- DESC
    {
      $order = "attachment.filename DESC";
      $order_description = "filename -- descending";
    }
    elsif ( $sort_order eq "I" ) # sort by folder name -- ASC
    {
      $order = "folder.name ASC";
      $order_description = "folder name -- ascending";
    }
    elsif ( $sort_order eq "J" ) # sort by folder name -- DESC
    {
      $order = "folder.name DESC";
      $order_description = "folder name -- descending";
    }
    else
    {
      $order = "attachment.id ASC";
      $order_description = "date added -- ascending";
    }

    print qq{order: <i>$order_description</i>};
    print qq{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;};
    print qq{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;};
    print qq{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;};
    if ( $hide eq 'yes' ) {
      print qq{[ <a href="$cgi_url?op=attachment%20administration&sort=$sort_order&hide=no">};
      print qq{show deleted/missing files</a> ]};
      }
    else {
      print qq{[ <a href="$cgi_url?op=attachment%20administration&sort=$sort_order&hide=yes">};
      print qq{hide deleted/missing files</a> ]};
      }
# set up the attachment management table 
    print qq{<table border=1>};
    print qq{<tr bgcolor="$fresh_title_background">};
    print qq{<th>id</th>};
    print qq{<th>filename }.
          qq{<font style="font-size: 60%; ">}.
          qq{(<a href="$cgi_url?op=attachment%20administration&sort=G&hide=$hide">A</a>}.
          qq{/}.
          qq{<a href="$cgi_url?op=attachment%20administration&sort=H&hide=$hide">D</a>)}.
          qq{</font></th>};
    print qq{<th>file size</th>};
    print qq{<th>user }.
          qq{<font style="font-size: 60%; ">}.
          qq{(<a href="$cgi_url?op=attachment%20administration&sort=E&hide=$hide">A</a>}.
          qq{/}.
          qq{<a href="$cgi_url?op=attachment%20administration&sort=F&hide=$hide">D</a>)}.
          qq{</font></th>};
    print qq{<th>folder }.
          qq{<font style="font-size: 60%; ">}.
          qq{(<a href="$cgi_url?op=attachment%20administration&sort=I&hide=$hide">A</a>}.
          qq{/}.
          qq{<a href="$cgi_url?op=attachment%20administration&sort=J&hide=$hide">D</a>)}.
          qq{</font></th>};
    print qq{<th>date added }.
          qq{<font style="font-size: 60%; ">}.
          qq{(<a href="$cgi_url?op=attachment%20administration&sort=A&hide=$hide">A</a>}.
          qq{/}.
          qq{<a href="$cgi_url?op=attachment%20administration&sort=B&hide=$hide">D</a>)}.
          qq{</font></th>};
    print qq{<th>last access }.
          qq{<font style="font-size: 60%; ">}.
          qq{(<a href="$cgi_url?op=attachment%20administration&sort=C&hide=$hide">A</a>}.
          qq{/}.
          qq{<a href="$cgi_url?op=attachment%20administration&sort=D&hide=$hide">D</a>)}.
          qq{</font></th>};
    print qq{<td></td}; # "select" boxes
    print qq{</tr>\n};

    # gather attachment information
    $query = qq{SELECT attachment.id,
                       user.name,
                       attachment.filename,
                       date_format(attachment.date, '%Y-%b-%d %H:%i:%s'),
                       date_format(attachment.access, '%Y-%b-%d %H:%i:%s'),
                       folder.name, 
                       attachment.access as last_access
                FROM attachment, user, message as parent, message as folder
                WHERE parent.id=attachment.message_id
                      AND user.id = parent.userid
                      AND folder.id=parent.folderid
                ORDER BY $order};
    $sth = $dbh->prepare ($query);
    my $db_files = int ($sth->execute () );
    my $alternate_line_color = 'off'; # turn off "green bar" for first line
    my ($attachment, $posters_name, $filename, $date, $access, $in_folder_name);
    while ( @ary = $sth->fetchrow_array () )
    {
      ($attachment, $posters_name, $filename, $date, $access, $in_folder_name) = @ary;
      my $local_file_name = sprintf("0%010d", $attachment);
      my $file_size = -s "$home_dir/re_files/$local_file_name" || 0;
      my $units = "b"; # set the units to default
      my $deleted = 'no'; # set the deleted flag to default
      unless ( -e  "$home_dir/re_files/$local_file_name") { # if the file does not exist....
        next if ( $hide eq 'yes' );   # AND "hide" is set to "yes", skip this row....
      }
      $access = '<center>-</center>' unless $access;
      $date = '<center>?</center>' unless $date;
# normalize file sizes to KB/MB/GB/TB
      $file_size = human_readable($file_size);
      $file_size =~ s/ /&nbsp;/g;

      # "green bar"
      if ($alternate_line_color eq "on") {
        print qq{<tr bgcolor="$fresh_message_background">}; # start table row
        $alternate_line_color = 'off'; # turn off "green bar" for next line
      }
      else {
        print qq{<tr>}; # start table row
        $alternate_line_color = 'on'; # turn on "green bar" for next line
      }

      print qq{<td>$attachment</td>}; # attachment id
      print qq{<td><a href="$cgi_url?op=norefresh&download=$attachment&filename=$filename">}.
            qq{$filename</a></td>}; # attachment filename
      if ( -e  "$home_dir/re_files/$local_file_name") {
        print qq{<td valign="top" align="right">$file_size</td>}; # attachment file size
      }
      else
      {
        print qq{<td valign="top" align="center">-</td>};
        $deleted = 'yes'; # set the deleted flag
      }
      print qq{<td valign="top">$posters_name</td>}; # attached to message id
      print qq{<td valign="top">$in_folder_name</td>}; # attachment stored in this folder
      $date =~ s/ /&nbsp;/g;
      print qq{<td valign="top">$date</td>}; # date added to db
      $access =~ s/ /&nbsp;/g;
      print qq{<td valign="top">$access</td>}; # number of times accessed
      print qq{<td valign="top">};
      print checkbox(-name=>'attachment_id',
                     -value=>"$attachment",
                     -label=>'',
                     ) unless $deleted eq 'yes';
      print qq{</td>}; # archive checkbox
      print qq{</tr>\n};
    }
    $sth->finish ();
    print qq{</table>},"\n";
    print qq{<font style="font-size: 80%; color: #BB0000; ">* Confirmation is required for all }.
          qq{attachment actions.</font>},br;
    print qq{<table><tr>};
    print qq{<td align="right"><b>Action:</b> </td><td>};
    print radio_group(-name => 'attachment action',
                      -values => ['No Action', 'delete', 'archive-iso', 'archive-tar', 'archive-zip'],
                      -default => 'No Action'),br;
    print qq{</td></tr>};
    print qq{<tr bgcolor="#FFDDDD"><td align="right">};
    print qq{<font style="font-size: 80%; color: #BB0000; ">*</font>};
    print qq{<b>Confirmation:</b> </td><td>};
    print radio_group(-name => 'action confirmation',
                      -values => ['No Action', 'delete', 'archive-iso', 'archive-tar', 'archive-zip'],
                      -default => 'No Action'),br;
    print qq{</td></tr></table>};
    print submit(-name=>'op', -value=>'Proceed with archive'),br;
    print qq{ [ <a href="$cgi_url">Back to <i>$title_tag</i></a> ]\n};
  }
  else
  {
    my @archive_name = <$home_dir/re_files/archive.*>;
# get the date/time of archive creation
    my $write_secs = (stat($archive_name[0]))[9];
    my $archive_size = (stat($archive_name[0]))[7];
    $archive_size = human_readable($archive_size);

    $archive_name[0] =~ s/\.\.\/re_files\///;
    print br;
    print "An archive file is in place. You must remove it to continue -- <b>$archive_name[0]</b>";
    printf "  ( %s )\n", scalar localtime($write_secs);

    print br,br;

    print qq{<a href="$cgi_url?op=remove%20archived%20attachments">Remove the attachments in }.
          qq{$archive_name[0]</a>},br;
    print qq{<a href="$cgi_url?op=dlarchive">Download $archive_name[0]</a> ($archive_size)},br;
    print qq{<a href="$cgi_url?op=delarchive">Delete $archive_name[0]</a>},br;
    print br;

# grab the contents of the archive to display
    $query = qq{SELECT contents FROM message WHERE folderid=0 and parentid=0 order by id desc limit 1};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    my ($archive_contents) = $sth->fetchrow_array ();
    $sth->finish ();

    print $archive_contents;
  }
  print end_form,end_html();
}
elsif ( $op eq 'remove archived attachments' && $name eq 'root') 
{
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Attachment Administration",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
  print start_form(-action=>$cgi_url, -method=>"post");
  print qq{<p class="body_text" style="font-size:90%; ">};
  print_title(); 
  print qq{<p class="body_text" style="font-size:200%; font-weight: bold; }.
        qq{text-align: center; margin-top: 1em; margin-bottom: 1em; ">}.
        qq{Attachment Administration</p>\n};

  print qq{<p class="body_text" style="font-size:90%; ">};
  print qq{ [ <a href="$cgi_url?op=attachment administration">Return to <b>Attachment }.
        qq{Administration</b></a> ]\n},br;
  print qq{ [ <a href="$cgi_url">Back to <i>$title_tag</i></a> ]\n},br;
  if ( -e  <$home_dir/re_files/archive.*>)  # if the archive file does not exist we will continue....
  {
    print br;

# grab the contents of the archive to parse
    $query = qq{SELECT contents FROM message WHERE folderid=0 and parentid=0 order by id desc limit 1};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    my ($archive_contents) = $sth->fetchrow_array ();
    $sth->finish ();

# move contents into an array
    my @archive_contents = split(/\n/, $archive_contents);
# set our XML flag to 'false'
    my $xml_flag = 0;
# parse the array, line by line
    foreach (@archive_contents)
    {
      $xml_flag = 1 if /<!-- BEGIN XML CONTENT -->/;
      next if $xml_flag == 0;
      if (/<local_filename>/)
      {
        s/<\/*local_filename>//g;
        s/^\s*//;
        unlink "$home_dir/re_files/$_";
        print qq{$_ .... <font color="#AA0000"><b>removed</b></font>},br;
      }
    }
  }
  else
  {
    print qq{The most recent archive has already been removed.};
  }
  print br;
  print qq{ [ <a href="$cgi_url?op=attachment administration">Return to <b>Attachment }.
        qq{Administration</b></a> ]\n},br;
  print qq{ [ <a href="$cgi_url">Back to <i>$title_tag</i></a> ]\n},br;
}
elsif ( $op eq 'Proceed with archive' && $name eq 'root') 
{
  my @attachment_id = param('attachment_id');
  my $action = param('attachment action') || 'No Action';
  my $confirmation = param('action confirmation') || 'No Confirmation';

  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Attachment Administration",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
  print qq{<p class="body_text">};
  print start_form(-action=>$cgi_url, -method=>"post");
  print qq{<p class="body_text">};
  print_title(); 
  print qq{<p class="body_text">};
  print qq{<center>};
  print qq{<p class="body_text" style="margin-bottom: 0; font-size:200%; }.
        qq{font-weight: bold; margin-top: 1em; ">Attachment Administration</p>};
  print qq{<p class="body_text" style="margin-top: 1em; font-size:150%; }.
        qq{font-weight: bold; margin-bottom: 0; ">};
  unless ( -e  <$home_dir/re_files/archive.*>)  # if the archive file does not exist we will continue....
  {
    if ($action ne $confirmation) 
    {
      print qq{<font color="#AA0000"><b><blink>***</blink> Confirmation }.
            qq{does not match action <blink>***</blink></b></font>},br;
      print qq{No action will be taken.};
      $action = 'none';
    }
    elsif ($action eq 'delete') 
    {
      print qq{<font color="#AA0000"><b>*** Delete ***</b></font>};
    }
    elsif ($action eq 'archive-iso') 
    {
      print qq{Archive to ISO image};
    }
    elsif ($action eq 'archive-tar') 
    {
      print qq{Archive to tar volume};
    }
    elsif ($action eq 'archive-zip') 
    {
      print qq{Archive to ZIP volume};
    }
    else 
    {
      print qq{*** No Archive Action was selected ***};
      $action = 'none';
    }
    print qq{</p>};
    print qq{</center>\n};

    print qq{<p class="body_text" style="font-size:90%; ">};
    print qq{ [ <a href="$cgi_url">Abort and return to <i>$title_tag</i></a> ]},br;
    print qq{\n\n},br;
    print qq{<!-- Here are the ids of the items we will be archiveing -->\n};

    my ($local_file_name, $file_size, @file_size, $archive_size, $attachment_where);
    foreach my $attachment_id (@attachment_id) 
    {
      # grab file size for getting an archive total size
      $local_file_name = sprintf("0%010d", $attachment_id);
      $file_size = -s "$home_dir/re_files/$local_file_name" || 0;
      $file_size[$attachment_id] = $file_size;
      $archive_size += $file_size;

      # concatinate OR statesment for db query
      $attachment_where .= " OR attachment.id=$attachment_id";
      print qq{<input type="hidden" name="attachment_id" value="$attachment_id">\n};
    }
    print qq{\n};

    print qq{<!-- The archive action is "$action" -->\n};
    print qq{<input type=hidden name="attachment action" value="$action">\n};
    print qq{<input type=hidden name="action confirmation" value="$action">\n\n};

    # trim and finish db query
    $attachment_where =~ s/^\ OR\ /(/;
    $attachment_where .= ")" if $attachment_where;
    $attachment_where = "1=2" unless $attachment_where;

    # archive size details
    my $units = "b";
# normalize file sizes to KB/MB/GB/TB
    $archive_size = human_readable($archive_size);

    print qq{<b>Archive Summary:</b>},br;
# set up the attachment management table 
    print qq{<table border=1>};
    print qq{<tr bgcolor="$fresh_title_background">};
    print qq{<th>id</th>};
    print qq{<th>filename</th>};
    print qq{<th>file size</th>};
    print qq{<th>user</th>};
    print qq{<th>folder</th>};
    print qq{<th>date added</th>};
    print qq{<th>last access</th>};
    print qq{<th>MD5sum</th>};
    print qq{</tr>\n};

    # gather attachment information
    $query = qq{SELECT attachment.id,
                       user.name,
                       attachment.filename,
                       date_format(attachment.date, '%Y-%b-%d %H:%i:%s'),
                       date_format(attachment.access, '%Y-%b-%d %H:%i:%s'),
                       folder.name, 
                       attachment.access as last_access
                FROM attachment, user, message as parent, message as folder
                WHERE parent.id=attachment.message_id
                      AND user.id = parent.userid
                      AND folder.id=parent.folderid
                      AND $attachment_where
                ORDER BY attachment.id};
    $sth = $dbh->prepare ($query);
    my $db_files = int ($sth->execute () );
    my ($attachment, $posters_name, $filename, $date, $access, $in_folder_name, $hide,
            $alternate_line_color, $md5sum);
    while ( @ary = $sth->fetchrow_array () )
    {
      ($attachment, $posters_name, $filename, $date, $access, $in_folder_name) = @ary;
      $local_file_name = sprintf("0%010d", $attachment);
      $file_size = -s "$home_dir/re_files/$local_file_name" || 0;
      $units = "b";
      unless ( -e  "$home_dir/re_files/$local_file_name")  # if the file does not exist....
      {
        next if ( $hide eq 'yes' );   # AND "hide" is set to "yes", skip this row....
      }
      $access = '<center>-</center>' unless $access;
      $date = '<center>?</center>' unless $date;
# normalize file sizes to KB/MB/GB/TB
      $file_size = human_readable($file_size);

      # "green bar"
      if ($alternate_line_color eq "on") 
      {
        print qq{<tr bgcolor="$fresh_message_background">}; # start table row
        $alternate_line_color = 'off'; # turn off "green bar" for next line
      }
      else 
      {
        print qq{<tr>}; # start table row
        $alternate_line_color = 'on'; # turn on "green bar" for next line
      }

      print qq{<td>$attachment</td>}; # attachment id
      print qq{<td><a href="$cgi_url?op=norefresh&download=$attachment&filename=$filename">}.
            qq{$filename</a></td>}; # attachment filename
      if ( -e  "$home_dir/re_files/$local_file_name") 
      {
        print qq{<td align="right">$file_size</td>}; # attachment file size
      }
      else
      {
        print qq{<td align="center">-</td>};
      }
      print qq{<td>$posters_name</td>}; # attached to message id
      print qq{<td>$in_folder_name</td>}; # attachment stored in this folder
      print qq{<td>$date</td>}; # date added to db
      print qq{<td>$access</td>}; # number of times accessed

      # grab the md5sum for this file
      $md5sum = `md5sum $home_dir/re_files/$local_file_name`;
      ($md5sum, $filename) = split(/\ /, $md5sum);
      print qq{<td>$md5sum</td>};
      print qq{</tr>\n};
    }
    $sth->finish ();
    print qq{</table>\n};
    print "<b>total $action size = $archive_size</b>",br,br;

    print qq{<b>Comments:</b>},br;
    print qq{<textarea name="archive_comment" cols="80" rows="15">};
    print qq{This area is provided for comments about the Archive/Deletion.};
    print qq{</textarea>},br;

    print qq{<font style="font-weight: bold; color:#AA0000; ">DELETIONS ARE PERMANENT }.
          qq{AND IRREVERSABLE. DO NOT CONTINUE IF UNCERTAIN.</font>},br
          if $action eq 'delete';
    print submit(-name=>'op', -value=>'create archive'),br
        unless $action eq 'none';
    print qq{ [ <a href="$cgi_url">Abort and return to <i>$title_tag</i></a> ]\n};
  }
  else
  {
    print "An archive file is in place. you must remove it to continue";
  }
  print end_form,end_html();
}
elsif ( $op eq 'create archive' && $name eq 'root') 
{
  my @attachment_id = param('attachment_id');
  my $action = param('attachment action') || 'No Action';
  my $confirmation = param('action confirmation') || 'No Confirmation';
  my $comments = param('archive_comment') || 'No Comments';

  unless ( -e  <$home_dir/re_files/archive.*>)  # if the archive file does not exist we will continue....
  {
    print header(-cookie=>[$cookie]);
    print start_html(-title=>"$title_tag - Attachment Administration",
                     -bgcolor=>"$page_background",
                     -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                     -style=>{-src=>'/re_default.css'});
    print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
    print qq{<p class="body_text">};
    print_title(); 
    print qq{<p class="body_text">};
    print qq{<center>};
    print qq{<p class="body_text" style="margin-bottom: 0; font-size:200%; }.
          qq{font-weight: bold; margin-top: 1em; ">Archive Creation</p>};
    if ($action ne $confirmation) 
    {
      print qq{<p class="body_text" style="margin-top: 1em; font-size:150%; }.
            qq{font-weight: bold; margin-bottom: 0; ">};
      print qq{<font color="#AA0000"><b><blink>***</blink> Confirmation }.
            qq{does not match action <blink>***</blink></b></font>},br;
      print qq{No action will be taken.};
      $action = 'none';
    }
    print br;
# set up the attachment management table 
    $query = qq{SELECT date_format(now(), '%Y-%b-%d %H:%i:%s')};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    my ($archive_date) = $sth->fetchrow_array ();
    $sth->finish ();

    print qq{</center>\n};

    print qq{<p class="body_text" style="font-size:90%; ">};
    print qq{ [ <a href="$cgi_url">Return to <i>$title_tag</i></a> ]},br;
    print qq{ [ <a href="$cgi_url?op=attachment administration">Return to <b>Attachment }.
          qq{Administration</b></a> ]\n},br;
    print qq{\n\n},br;

    my ($local_file_name, $file_size, @file_size, $attachment_where, $archive_size);
    foreach my $attachment_id (@attachment_id) 
    {
      # grab file size for getting an archive total size
      $local_file_name = sprintf("0%010d", $attachment_id);
      $file_size = -s "$home_dir/re_files/$local_file_name" || 0;
      $file_size[$attachment_id] = $file_size;
      $archive_size += $file_size;

      # concatinate OR statesment for db query
      $attachment_where .= " OR attachment.id=$attachment_id";
    }
    print qq{\n};

    # trim and finish db query
    $attachment_where =~ s/^\ OR\ /(/;
    $attachment_where .= ")" if $attachment_where;
    $attachment_where = "1=2" unless $attachment_where;

    # archive size details
    my $units = "b";
# normalize file sizes to KB/MB/GB/TB
    $archive_size = human_readable($archive_size);

    my $doc_contents =  qq{\n<!-- BEGIN HTML CONTENT -->\n\n};
    $doc_contents .= qq{<b>Archive Summary:</b><br>\n};
    $doc_contents .= qq{<p class="body_text" style="margin-top: 0; }.
                     qq{font-size:110%; font-weight: bold; margin-bottom: 0; ">};
    my $title_contents = qq{$archive_date - $action - $title_tag};
    $doc_contents .= qq{$title_contents};
    $doc_contents .= qq{</p>};
# set up the attachment management table 
    $doc_contents .= qq{<table border=1>\n};
    $doc_contents .= qq{<tr bgcolor="$fresh_title_background">};
    $doc_contents .= qq{<th>id</th>};
    $doc_contents .= qq{<th>local file</th>};
    $doc_contents .= qq{<th>filename</th>};
    $doc_contents .= qq{<th>file size</th>};
    $doc_contents .= qq{<th>user</th>};
    $doc_contents .= qq{<th>folder</th>};
    $doc_contents .= qq{<th>date added</th>};
    $doc_contents .= qq{<th>last access</th>};
    $doc_contents .= qq{<th>MD5sum</th>};
    $doc_contents .= qq{</tr>\n};
    my $alternate_line_color = 'on'; # turn on "green bar" for first line of summary

    my $xml_contents =  qq{<!-- BEGIN XML CONTENT -->\n};
    $xml_contents .= qq{<!--\n};
    $xml_contents .= qq{<archive type="$action" date="$archive_date" site="$title_tag">\n};

# if we're creating an archive, we'll create the archive direcotry
    if ( $action eq 'archive-iso' || $action eq 'archive-tar' || $action eq 'archive-zip' )
    {
      mkdir "$home_dir/re_files/archive", 0755;
    }

    # gather attachment information
    $query = qq{SELECT attachment.id,
                       user.name,
                       attachment.filename,
                       date_format(attachment.date, '%Y-%b-%d %H:%i:%s'),
                       date_format(attachment.access, '%Y-%b-%d %H:%i:%s'),
                       folder.name, 
                       attachment.access as last_access
                FROM attachment, user, message as parent, message as folder
                WHERE parent.id=attachment.message_id
                      AND user.id = parent.userid
                      AND folder.id=parent.folderid
                      AND $attachment_where
                ORDER BY attachment.id};
    $sth = $dbh->prepare ($query);
    my $db_files = int ($sth->execute () );
    my ($attachment, $posters_name, $filename, $date, $access, $in_folder_name);
    while ( @ary = $sth->fetchrow_array () )
    {
      ($attachment, $posters_name, $filename, $date, $access, $in_folder_name) = @ary;
      $local_file_name = sprintf("0%010d", $attachment);
      $file_size = -s "$home_dir/re_files/$local_file_name" || 0;
      $units = "b";
      $access = '-' unless $access;
      $date = '?' unless $date;
# normalize file sizes to KB/MB/GB/TB
      $file_size = human_readable($file_size);

      # "green bar"
      if ($alternate_line_color eq "on") 
      {
        $doc_contents .= qq{<tr bgcolor="$fresh_message_background">}; # start table row
        $alternate_line_color = 'off'; # turn off "green bar" for next line
      }
      else 
      {
        $doc_contents .= qq{<tr>}; # start table row
        $alternate_line_color = 'on'; # turn on "green bar" for next line
      }
      $xml_contents .= qq{  <attachment>\n};

      $doc_contents .= qq{<td>$attachment</td>}; # attachment id
      $xml_contents .= qq{    <id>$attachment</id>\n};
      $doc_contents .= qq{<td>$local_file_name</td>}; # attachment filename
      $xml_contents .= qq{    <local_filename>$local_file_name</local_filename>\n};
      $doc_contents .= qq{<td>$filename</td>}; # attachment filename
      $xml_contents .= qq{    <filename>$filename</filename>\n};
      if ( -e  "$home_dir/re_files/$local_file_name") 
      {
        $doc_contents .= qq{<td align="right">$file_size</td>}; # attachment file size
        $file_size =~ s/\&nbsp;//;
        $xml_contents .= qq{    <filesize>$file_size</filesize>\n};
      }
      else
      {
        $doc_contents .= qq{<td align="center">-</td>};
        $xml_contents .= qq{    <filesize>-</filesize>\n};
      }
      $doc_contents .= qq{<td>$posters_name</td>}; # attached to message id
      $xml_contents .= qq{    <posters_name>$posters_name</posters_name>\n};
      $doc_contents .= qq{<td>$in_folder_name</td>}; # attachment stored in this folder
      $xml_contents .= qq{    <folder_name>$in_folder_name</folder_name>\n};
      $doc_contents .= qq{<td align="center">$date</td>}; # date added to db
      $xml_contents .= qq{    <date>$date</date>\n};
      $doc_contents .= qq{<td align="center">$access</td>}; # number of times accessed
      $xml_contents .= qq{    <last_access>$access</last_access>\n};

      # grab the md5sum for this file
      my $md5sum = `md5sum $home_dir/re_files/$local_file_name`;
      ($md5sum, $filename) = split(/\ /, $md5sum);
      $doc_contents .= qq{<td>$md5sum</td>};
      $xml_contents .= qq{    <md5sum>$md5sum</md5sum>\n};
      $doc_contents .= qq{</tr>\n};
      $xml_contents .= qq{  </attachment>\n};
      
# take action on the attachment
      if ($action eq 'delete') 
      {
# delete the file named in $local_file_name
        unlink "$home_dir/re_files/$local_file_name";
      }
      elsif ( $action eq 'archive-iso' || $action eq 'archive-tar' || $action eq 'archive-zip')  
      {
# delete the file named in $local_file_name
        link "$home_dir/re_files/$local_file_name","$home_dir/re_files/archive/$local_file_name";
      }
    }
    $sth->finish ();
    $doc_contents .= qq{</table>\n};
    $doc_contents .= "<b>total $action size = $archive_size</b><br><br>\n";

    $doc_contents .= qq{<b>Comments:</b><br>\n};
    $xml_contents .= qq{  <comments>\n$comments\n  </comments>\n};
    $doc_contents .= qq{<table border=1 width=100%><tr><td>\n};
    $comments =~ s/\r/\r<br>/g;
    $doc_contents .= qq{$comments};
    $doc_contents .= qq{</td></tr></table>\n},br;
    $xml_contents .= qq{</archive>\n};

    $doc_contents .= qq{\n<!-- END HTML CONTENT -->\n\n\n};
    $xml_contents .= qq{-->\n};
    $xml_contents .= qq{<!-- END XML CONTENT -->\n};

    print $doc_contents;
    if ( $action eq 'archive-iso' || $action eq 'archive-tar' || $action eq 'archive-zip')  
    {
      open (ARCHIVE_HTML, ">$home_dir/re_files/archive/index.html");
      print ARCHIVE_HTML $doc_contents;
      close ARCHIVE_HTML;
    }
    print $xml_contents;
    if ( $action eq 'archive-iso' || $action eq 'archive-tar' || $action eq 'archive-zip')  
    {
      open (ARCHIVE_XML, ">$home_dir/re_files/archive/index.xml");
      print ARCHIVE_XML $xml_contents;
      close ARCHIVE_XML;
    }

    my $archive_summary = "$doc_contents\n$xml_contents";
    $archive_summary =~ s/<br\ *\/*>//g;
    $archive_summary =~ s/'/\\'/g;
    $title_contents =~ s/'/\\'/g;
    my $query = qq{INSERT into message
                   SET name='$title_contents',
                       contents='$archive_summary',
                       groupr='N',
                       otherr='N',
                       parentid=0,
                       folderid=0,
                       time=date_format(now(), '%Y%m%d%H%i%s')
                    };
    my $sth = $dbh->prepare ($query);
    $sth->execute ();

    if ( $action eq 'archive-iso' )
    {
      `mkisofs -iso-level 3 -r -o $home_dir/re_files/archive.iso $home_dir/re_files/archive/`;
      `rm $home_dir/re_files/archive -rf`;
    }
    elsif ( $action eq 'archive-tar' )
    {
      `tar -C $home_dir/re_files/ -cf $home_dir/re_files/archive.tar archive`;
      `rm $home_dir/re_files/archive -rf`;
    }
    elsif ( $action eq 'archive-zip')  
    {
      `zip -1 $home_dir/re_files/archive.zip $home_dir/re_files/archive/*`;
      `rm $home_dir/re_files/archive -rf`;
    }

    print qq{ [ <a href="$cgi_url?op=attachment administration">Return to <b>Attachment }.
          qq{Administration</b></a> ]\n},br;
    print qq{ [ <a href="$cgi_url">Return to <i>$title_tag</i></a> ]\n};
  }
  else
  {
    print redirect(-cookie=>[$cookie], -url=>"$cgi_url?op=attachment%20administration");
  }
}
elsif ( $op eq 'dlarchive' && $name eq 'root') 
{
  my @archive_name = <$home_dir/re_files/archive.*>;
  my $local_file_name = $archive_name[0];
  $archive_name[0] =~ s/\.\.\/re_files\///;

  my $size = -s "$home_dir/re_files/$local_file_name" || 0;
  print qq{Content-type: application/octet-stream\n};
  print qq{Content-disposition: attachment; filename="$archive_name[0]"\n};
  print qq{Content-length: $size\n\n};
  open (ATTACHMENT, "$home_dir/re_files/$local_file_name") || die "something's wrong. can't open\n$!\n";
  while (<ATTACHMENT>) { print; }
  close (ATTACHMENT);
}
elsif ( $op eq 'delarchive' && $name eq 'root') 
{
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Delete Attachment Archive",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
  print qq{<p class="body_text">};
  print_title(); 
  print qq{<p class="body_text">};
  print qq{<center>};
  print qq{<p class="body_text" style="margin-bottom: 0; font-size:200%; }.
        qq{font-weight: bold; margin-top: 1em; ">Delete Attachment Archive</p>};

  print br,br;

  my @archive_name = <$home_dir/re_files/archive.*>;
# get the date/time of archive creation
  my $write_secs = (stat($archive_name[0]))[9];

  $archive_name[0] =~ s/\.\.\/re_files\///;
  print "Removing <b>$archive_name[0]</b>";
  printf "  ( %s )....\n", scalar localtime($write_secs);

  print br,br;

  unlink ("$home_dir/re_files/$archive_name[0]") || die "died trying to delete attachment archive";
  print "Archive removed.",br;
  print qq{ [ <a href="$cgi_url?op=attachment%20administration">Back to Attachment Administration</a> ]\n},br;
  print qq{ [ <a href="$cgi_url">Back to <i>$title_tag</i></a> ]\n},br;
}

# search all published stories -- return in order of ranking
elsif($op eq 'search') {
  print header(-cookie=>[$cookie]) if $op ne 'login';
  print start_html(-title=>"$title_tag - Search",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});
  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
  print start_form(-action=>$cgi_url, -method=>"post");
  print_folder();
  print_title(); 
  print "<center><h1>Search</h1></center>";

  print start_form(-action=>'$cgi_url', method=>'GET');
  print "Search: <input type=text name=searchquery value='$searchquery'>";
  print qq{<input type=hidden name=op value=search>};
  print submit(-name=>'button', -value=>'search'),br;
  print end_form();

  $searchquery =~ s/\\/\\\\/g;
  $searchquery =~ s/"/\\"/g;
  $searchquery =~ qq{$searchquery};

  # sorting search results by means other than score
  my $sort_order = param('sort') || "A";
  my ($order, $order_description);
  if ( $sort_order eq "A" ) # sort by score. we'll duplicate this at the bottom
  {
    $order = "score";
    $order_description = "";
  }
  elsif ( $sort_order eq "B" ) # sort by date/time DESC
  {
    $order = "message.time DESC";
    $order_description = "sorted by date/time, most recent first<br>";
  }
  elsif ( $sort_order eq "C" ) # sort by date/time ASC
  {
    $order = "message.time ASC";
    $order_description = "sorted by date/time, oldest first<br>";
  }
  elsif ( $sort_order eq "D" ) # sort by user name ASC
  {
    $order = "user.name ASC";
    $order_description = "sorted by username, ascending<br>";
  }
  elsif ( $sort_order eq "E" ) # sort by user name DESC
  {
    $order = "user.name DESC";
    $order_description = "sorted by username, descending<br>";
  }
  elsif ( $sort_order eq "F" ) # sort by folder name ASC
  {
    $order = "folder.name ASC";
    $order_description = "sorted by folder name, ascending<br>";
  }
  elsif ( $sort_order eq "G" ) # sort by folder name DESC
  {
    $order = "folder.name DESC";
    $order_description = "sorted by folder name, descending<br>";
  }
  else
  {
    $order = "score DESC";
  }

  if ($searchquery) {
    # limit results to 25 per page
    my $results_limit = 25;

    # NEW Wed Jan 15 00:27:24 MST 2003
    # AND searches only on all terms
    # First, we'll strip out any non alpha-numeric chars + (-@$%_)
    $searchquery =~ s/[^A-Za-z0-9 \-\@\$\%_]//;
# trim off any leading or trailing spaces
    $searchquery =~ s/^\s+//;
    $searchquery =~ s/\s+$//;
    # split string on spaces
    my @terms = split(/\s+/, $searchquery);
    # build MATCH AGAINST queries
    my (@query, $query, $query_counter, $term);
    foreach my $term (@terms)
    {
      $query[$query_counter] = qq{MATCH (message.name, message.contents) AGAINST ("$term")};
      $query_counter++;
      last if ($query_counter >= 10);
    }
# form final query with ANDs
    my $full_text_query = join (" AND ", @query);
    my $highlight_query = join (",",@terms); # we'll use this in the link for highlighting results


    # issue query
    # calculate number of pages of search results
    $query = qq{
                SELECT message.id
                FROM message message, user, message folder, members
                # WHERE MATCH (message.name, message.contents) AGAINST ("$searchquery")
                WHERE ( $full_text_query )
                  AND message.userid = user.id
                  AND message.folderid = folder.id
                  AND ( ( folder.groupid = members.groupid AND members.userid = $our_userid )
                      OR folder.otherr = 'Y' )
                GROUP BY message.id
                };
    my $sth = $dbh -> prepare ($query) || die $dbh->errstr;
    my $results_count = int ( $sth->execute() );
    $sth->finish ();
    $pages = ceil($results_count/$results_limit);
    $page = param('page') || 1;
    $page = $pages if $page > $pages;
    $page = 1 if $page < 1;

    # page shifting code...
    my $first_display_page = 1; # set default first page
    my $last_display_page = $pages; # set default last page
    if ( $pages > 20 )
    {
      $last_display_page = 20;
      if ( $page > 10 )
      {
        $last_display_page = $page + 9;
        $first_display_page = $page - 10;
        if ($last_display_page > $pages )
        {
          $last_display_page = $pages;
          $first_display_page = $pages - 19;
        }
      }
    }

    my $last_page = $page - 1;
    print qq{<p class="navigation" style="font-size: 80%; ">};
    print qq{$order_description};
    print qq{Order results by: };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=A">}
          unless $sort_order eq "A";
    print qq{relivence};
    print qq{</a> };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=B">}
          unless $sort_order eq "B";
    print qq{time (desc)};
    print qq{</a> };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=C">}
          unless $sort_order eq "C";
    print qq{time (asc)};
    print qq{</a> };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=D">}
          unless $sort_order eq "D";
    print qq{user (asc)};
    print qq{</a> };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=E">}
          unless $sort_order eq "E";
    print qq{user (desc)};
    print qq{</a> };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=F">} 
          unless $sort_order eq "F";
    print qq{folder (asc)};
    print qq{</a> };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=G">} 
          unless $sort_order eq "G";
    print qq{folder (desc)};
    print qq{</a> };
    print br;
    print qq{[$results_count hits -- page $page of $pages] };
    print qq{&nbsp;&nbsp;&nbsp;};
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&page=$last_page&}.
          qq{sort=$sort_order" title="go to previous page (page $last_page)">&lt;&lt;}.
          qq{</a>&nbsp;&nbsp;} if $page > 1;
    # for ($i=1; $i<=$pages; $i++) {
    for ($i=$first_display_page; $i<=$last_display_page; $i++) {
      print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&page=$i&sort=$sort_order" }.
            qq{title="jump to page $i">} if $i != $page;
      print qq{<b><font style="background-color: $fresh_title_background; }.
            qq{padding-left: 4px; padding-right: 4px; ">}
            if ( $i == $page && $pages != 1 );
      print qq{$i} if $pages != 1;
      print qq{</font></b>} if ( $i == $page && $pages != 1 );
      print qq{</a>} if $i != $page;
      print qq{&nbsp;&nbsp;};
      }
    my $next_page = $page + 1;
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&page=$next_page&}.
          qq{sort=$sort_order" title="go to next page (page $next_page)">&gt;&gt;</a>}
            if $page < $pages;
    print qq{</p>};

    my $offset_results = $results_limit*($page-1);

    $limit = qq{LIMIT $offset_results,$results_limit}  # if $limit is defined, create LIMIT
             unless ( defined($limit) || $page == -1 ); # query string
                                                            

    $query = qq{
                SELECT message.id, message.name, date_format(message.time, '%d.%b.%Y'),
                      user.name, folder.name, message.folder, folder.parentid,
                      MATCH (message.name, message.contents) AGAINST ('$searchquery') AS score
                FROM message message, user, message folder, members
                # WHERE MATCH (message.name, message.contents) AGAINST ("$searchquery")
                WHERE $full_text_query
                  AND message.userid = user.id
                  AND message.folderid = folder.id
                  AND ( ( folder.groupid = members.groupid
                          AND members.userid = $our_userid
                          AND ( folder.groupr = 'Y' OR folder.userid = $our_userid ) )
                      OR folder.otherr = 'Y' )
                GROUP BY message.id
                ORDER BY $order
                $limit
                };
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    my $rv = $sth->rows();
    unless ($rv) {
      print "No results found for \"$searchquery\"",br;
      }
    my ($message_id, $name, $time, $message_user, $message_folder, $folder_test, $message_folderid, $path);
    while ( @ary = $sth->fetchrow_array () ) {
      ($message_id, $name, $time, $message_user, $message_folder,
       $folder_test, $message_folderid) = @ary;
      my $message_root = find_thread_root($message_id);
      # $subject =~ s/%3F/?/g;
      if ($folder_test eq 'Y') {
        $path = regress_folders($message_id) if $folder_test eq 'Y';
        # print qq{<img src="/icons/small/dir.gif" width=16 }.
        print qq{<img src="/icons/crystal/16x16/filesystems/folder.png" width=16 }.
              qq{height=16 border=0 alt="[$name]">};
        print qq{<b><a href="$cgi_url?op=expand&message=$message_root&}.
              qq{folder=$path&mark=$highlight_query">}.
              qq{$name</a></b>, by <b>$message_user</b> on $time},br,"\n";
        }
      else {
        $path = regress_folders($message_folderid);
        # print qq{<img src="/icons/small/generic.gif" width="16" }.
        print qq{<img src="/icons/crystal/16x16/actions/filenew2.png" width="16" }.
              qq{height="16" border="0" alt="[$name]">};
        print qq{<b><a href="$cgi_url?op=expand\&message=$message_root&}.
              qq{folder=$path$message_folder\&searchresult=$message_id}.
              qq{&mark=$highlight_query#$message_id">}.
              qq{$name</a></b> (in $message_folder), by <b>$message_user</b> on $time},br,"\n";
        }
      }
    $sth->finish ();

    $last_page = $page - 1;
    print qq{<p class="navigation" style="font-size: 80%; ">};
    print qq{[$results_count hits -- page $page of $pages] };
    print qq{&nbsp;&nbsp;&nbsp;};
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&page=$last_page&}.
          qq{sort=$sort_order" title="go to previous page (page $last_page)">&lt;&lt;}.
          qq{</a>&nbsp;&nbsp;} if $page > 1;
    # for ($i=1; $i<=$pages; $i++) {
    for ($i=$first_display_page; $i<=$last_display_page; $i++) {
      print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&page=$i&sort=$sort_order" }.
            qq{title="jump to page $i">} if $i != $page;
      print qq{<b><font style="background-color: $fresh_title_background; }.
            qq{padding-left: 4px; padding-right: 4px; ">} 
            if ( $i == $page && $pages != 1 );
      print qq{$i} if $pages != 1;
      print qq{</font></b>} if ( $i == $page && $pages != 1 );
      print qq{</a>} if $i != $page;
      print qq{&nbsp;&nbsp;};
      }
    $next_page = $page + 1;
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&page=$next_page&}.
          qq{sort=$sort_order" title="go to next page (page $next_page)">&gt;&gt;</a>}
            if $page < $pages;
    print qq{</p>};

    }


  $sth->finish ();
  $dbh->disconnect ();

  print << '  END_OF_TEXT';
  <br>
  <center><table border=1 width=85%><tr><td bgcolor="EEEEEE">
  <font size=-1><em>Due to the limitations of the database engine, words of less than
  four (4) characters are ignored in searches. We regret this limitation. While there
  is a solution to this problem, doing so will result in greater demands on the 
  server.</em></font>
  </td></tr></table></center>
  END_OF_TEXT
  print "</td><td width=\"20%\"  valign=top>";
  print end_html();
  }

elsif($op eq 'norefresh' && $download) {
  $query = qq{SELECT attachment.filename
              FROM user, members, message as folder, message as messaget, attachment
              WHERE attachment.id = $download
                AND folder.id = messaget.folderid
                AND messaget.id = attachment.message_id
                AND ( ( ($our_userid = members.userid
                  AND members.groupid = folder.groupid
                  AND folder.groupr = "Y")
                    OR folder.userid = $our_userid )
                  OR folder.otherr = "Y" )
                GROUP BY attachment.id};
#                AND ( folder.groupr = "Y" OR folder.userid = $our_userid )
#                OR folder.otherr = "Y"};
  $sth = $dbh->prepare ($query);
  my $rv = $sth->execute();
  my ($filename) = $sth->fetchrow_array ();
  $sth->finish ();
  if ($filename) { # if a filename is returned, send the file
    
    # get file name extension from file name
    my (@file_ext) = split (/\./, $filename);
    $file_ext[$#file_ext] =~ tr/A-Z/a-z/; # translate file ext to lower case for mime type matching
    
    my $send_mimetype = "application/octet-stream"; # set default mime type

    #find the mime type definition from /etc/mime.types
    my ($mimetype, @ext);
    open (MIMETYPES, "/etc/mime.types"); # || die "What?! $!\n\n";
    MIME: while (<MIMETYPES>) {
      ($mimetype, @ext) = split (/\s+/);
      foreach my $extension (@ext) {
        $send_mimetype = $mimetype if $extension eq $file_ext[$#file_ext];
        last MIME if $extension eq $file_ext[$#file_ext];
        }
      }
    close(MIMETYPES);

    my $local_file_name = sprintf("0%010d", $download);
    my $size;
    $local_file_name =~ s#[^\w.-_+]#_#g;
    if ( -e "$home_dir/re_files/$local_file_name" ) {
      $size = -s "$home_dir/re_files/$local_file_name" || 0;
      print qq{Content-type: $mimetype\n};
      unless ($mimetype eq 'text/html'  # the following mime types won't go out as attachments
              || $mimetype eq 'text/plain' # this will just go out as in-line docs
              || $mimetype eq 'image/gif'
              || $mimetype eq 'image/jpeg'
              || $mimetype eq 'image/png')
      {
        print qq{Content-disposition: attachment; filename="$filename"\n};
      }
      print qq{Content-length: $size\n\n};
      open (ATTACHMENT, "$home_dir/re_files/$local_file_name") || die "darn! can't open\n$!\n";
      while (<ATTACHMENT>) { print; }
      close (ATTACHMENT);
      $query = qq{UPDATE attachment
                  SET access= date_format(NOW(), "%Y%m%d%H%i%s")
                  WHERE id = $download};
      $sth = $dbh->prepare ($query);
      $sth->execute();
      $sth->finish ();
      }
    else {
      print header(-cookie=>[$cookie]);
      print start_html(-title=>"RealizationEngine Error - File not available",
                       -bgcolor=>"$page_background",
                       -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                       -style=>{-src=>'/re_default.css'});
      print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
      print h1("RealizationEngine Error - File Not Available");
      print "This file does is no longer available on the system. Check with your ".
            "RealizationEngine administrator to see if the file is available in the local ".
            "archive, and if it is still available.",br,br;
      print h3("File Information");
      print "File id: $local_file_name",br;
      print "File name: $filename",br;
      print "It may be helpful to print this page and have it available when talking to ".
            "your RealizationEngine administrator.";
      }
    }
  else { # else, send appropriate page headers, and inform of error
    print header(), "Uh, no! Some error occurred. Please alert your administrator",br,br;
    # print "$query";
    }
  }
elsif ( $op eq 'change settings' && $name eq 'root' ) {
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Change Settings",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});

  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the
                                                              # favicon.ico
  $query = qq{SELECT name, value
              FROM settings};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  print start_form(-action=>"$cgi_url", -method=>"post");
  print "Settings:",br,"\n";
  print "<table border=1>";
  my ($name, $value);
  while ( @ary = $sth->fetchrow_array () ) {
    ($name, $value) = @ary;
    $value{$name} = $value;
    print "<tr><td align=right>$name: </td>\n";
    if ($name eq "title") {
      my $display_title = $value;
      $display_title =~ s/\$(\w+)/$value{$1}/g;
      print "<td><b>$display_title</b>".
            qq{<center><font class="navigation"><i>(may not dispaly properly)</i></font></center></td>\n};
      print "<td>",textarea(-name=>$name, -value=>$value, -cols=>50, -rows=>10, -wrap=>"off"),
            "</td></tr>\n";
      }
    else {
      print "<td><b>$value</b></td>\n";
      print "<td>",textfield(-name=>$name, -value=>$value, size=>50),"</td></tr>\n";
      }
    }
  $sth->finish ();
  $dbh->disconnect ();

  print "</table>";
  print submit(-name=>"op", -value=>"update settings");
  print end_form();

}
elsif ( $op eq 'update settings' && $name eq 'root' ) {
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$title_tag - Update Settings",
                   -bgcolor=>"$page_background",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>'/re_default.css'});

  print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
  print qq{<p class="body_text">};
  my @names = param();
  foreach $name (@names) {
    next if ($name eq 'user' || $name eq 'pass' || $name eq 'database' || $name eq 'op');
    my $value = param($name);
    $value =~ s/'/\\'/g;
    # print "$name ==> $value",br;
    $query = qq{UPDATE settings
                SET value='$value'
                WHERE name='$name'};
    my $sth = $dbh->prepare ($query);
    $sth->execute ();
    print "$query",br;
    }

  print hr();
  print qq{<a href="$cgi_url">Return to $title_tag</a>};
  print end_form();
  print end_html();
  print qq{</p>};
  }

else 
{
# my $t0 = new Benchmark;
# my ($t1, $t2, $t3);
  my ($childcount, $todays_children, $weeks_children, $sessions_children, $last_post_time);
  my ($folder_count) = 0;
  unless ( $op eq 'publish' )
  {
    unless ( $op eq 'clean output' )
    {
      $expandmessage = param('message') || 0;
      $thread_style = $user_thread_style unless $user_thread_style == 0;
      $expand = param('expand') || $thread_style;
      print header(-cookie=>[$cookie],
                 -cache_control=> "no-cache",
                 -cache_control=> "max-age=0") if $op ne 'login';


      print start_html(-title=>"$title_tag - $folder", 
                 -bgcolor=>"$page_background",
                 -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                 -expires=> "Mon, 26 Jul 1997 05:00:00 GMT",
                 -cache_control=> "post-check=0,pre-check=0",
                 -pragma=> "no-cache",
                 -style=>{-src=>'/re_default.css'}),"\n";

      # print qq{<font style="font-size: 200%; font-weight: bold; ">$folder</font>},br;
      print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n};
      print start_form(-action=>"$cgi_url", -method=>"post");
      print qq{<p style="margin-top:0; margin-bottom:0; ">};
      print_title();
      print qq{<p style="margin-top:0; margin-bottom:0; ">};
      print_folder();
      if( $name ) 
      {
        print "Welcome back, $name";
        print qq{<span style="font-size:80%; ">};
        print submit(-name=>"op", -value=>"account maintenance",
                     -title=>"change your password or e-mail address");
        print "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;";
        print "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;";
        print "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp&nbsp;&nbsp;";
        print submit(-name=>"op", -value=>"logout",
                     -title=>"logout of the system");
        print "</span>";
      }
      else 
      {
        print qq{<table style="background-color: $fresh_title_background; "}.
              qq{ border=1 cellpadding=5><tr><td>};
        print qq{<span style="font-size:80%; background-color: $fresh_title_background; ">};
        print submit(-name=>"op", -value=>"login");
        print qq{&nbsp;&nbsp;&nbsp;&nbsp; new users: };
        print submit(-name=>"op", -value=>"create new account");
        print qq{</span>};
        print qq{<input type="hidden" name="folder" value="$folder">};
        print qq{</td></tr></table>};
      }
      if ($name eq 'root') 
      {
        print qq{<table border=1 cellpadding=5><tr><td bgcolor="$fresh_message_background">};
        print "[ <a href=\"$cgi_url?op=user%20administration\">user administration</a> ]";
        print "[ <a href=\"$cgi_url?op=group%20administration\">group administration</a> ]";
        print "[ <a href=\"$cgi_url?op=change%20settings\">settings administration</a> ]";
        print  "</td></tr></table>";
      }

      unless ( $op eq 'isolate')
      {
        print qq{<table border=0 bgcolor="$fresh_message_background" class="navigation"><tr><td valign=top>};

        # Print new message button
        if ( (($otherw eq 'Y' || $our_userid == $ownerid || $name eq 'root') || 
                ($groupmember eq 'Y' && $groupw eq 'Y')) && $open eq 'Y' && $our_userid > 0 ) 
        {
          print "<input type=hidden name=\"folder\" value=\"$folder\">";
          print qq{<span style="margin-top:0; margin-bottom:0; ">};
          print "<img src=\"/icons/crystal/22x22/actions/edit.png\" width=22 height=22 border=0>",
                submit(-name=>"op", -value=>"new entry", -title=>"create new message"),br;
          print "</span>";
        }

        print "<img src=\"/icons/crystal/22x22/actions/filefind.png\" width=22 height=22 border=0>",
              submit(-name=>"op", -value=>"search", -title=>"search message base"),br
              if $allow_search eq 'Y';

        # Print new folder button
        if ( (($otherw eq 'Y' || $our_userid == $ownerid || $name eq 'root') || 
                ($groupmember eq 'Y' && $groupw eq 'Y')) && $open eq 'Y' && $our_userid > 0 ) 
        {
          print "<input type=hidden name=\"folder\" value=\"$folder\">";
          print qq{<span style="margin-top:0; margin-bottom:0; ">};
          print "<img src=\"/icons/crystal/22x22/actions/folder_new.png\" width=22 height=22 border=0>",
                submit(-name=>"op", -value=>"new folder", -title=>"create new folder"),br;
          print "</span>";
        }

        print qq{<div style="text-align: center; ">Current system time:},br,
              qq{$current_system_time</div>}
              if $current_system_time;
        print "</td><td valign=top>";

        # Print folders
        $parentfolder =~ s/^\/// if $parentfolder;
        my @parentfolder = split ("/", $parentfolder) if $parentfolder;
        my $parentindex = @folder - 2;
        my ($parentname);
        if (@folder > 1) 
        {
          $parentname = $folder[$parentindex];
        }
        else
        {
          $parentname = $root_folder_name;
        }
        $parentfolder =~ s/\ /\%20/g if $parentfolder;

        my $last_post_time = 0;

        if ( $folderid != 1 ) 
        {
          # get the parent id
          my $query = qq{SELECT parentid
                         FROM message
                         WHERE id=$folderid};
          my $sth = $dbh->prepare ($query);
          $sth->execute ();
          my ($parent_folderid) = $sth->fetchrow_array ();
          $sth->finish ();

          # get the info on the parent folder
          $query = qq{SELECT groupr, otherr, groupid, userid
                         FROM message
                         WHERE id=$parent_folderid};
          $sth = $dbh->prepare ($query);
          $sth->execute ();

          my ($parent_groupr, $parent_otherr, $parent_groupid, $parent_userid) = $sth->fetchrow_array ();
          $sth->finish ();

          # check to see if user has permission to see messages in parent folder
          my $parent_group_member;
          if ( defined($users_groups{$parent_groupid}) )
          {
            $parent_group_member = 'Y' ;
          }
          $parent_group_member = 'Y' if $name eq 'root'; # root overrides parent membership
          $parent_group_member = 'N' unless $name; # root overrides parent membership
            
          # Print (open) folder navigation
          $folder_array[$folder_count] = "<a href=$cgi_url?folder=$parentfolder&expand=$expand>";
          $folder_array[$folder_count] .= "<img src=\"/icons/crystal/16x16/actions/up.png\" ".
                                          "width=16 height=16 border=0>$parentname";
          $folder_array[$folder_count] .= "</a>";

          if ( $parent_group_member eq 'Y' || $parent_otherr eq 'Y' || $name eq 'root' )
          {
            ($childcount, $todays_children, $weeks_children, $sessions_children,
              $last_post_time) = countchildren($parent_folderid);
            if ($last_post_time)
            {
              $folder_array[$folder_count] .= " ($childcount/<font color=#3333dd><b>$todays_children</b></font>/";
              $folder_array[$folder_count] .= "<font color=#EE0000><b>" if $sessions_children > 0;
              $folder_array[$folder_count] .= "$sessions_children";
              $folder_array[$folder_count] .= "</b></font>" if $sessions_children > 0;
              $folder_array[$folder_count] .= ")";
            }
          }
          $folder_array[$folder_count] .= br if $parentfolder;
          $folder_count ++ if $parentfolder;
        }
        if ($otherr eq 'Y' || $our_userid == $ownerid || $name eq 'root'
              || $folder eq $root_folder_name   # if we're in the root folder, we have to print
                                                # the sub folders, for everyone.
              || ( $groupmember eq 'Y' && $groupr eq 'Y'))
        {

          # display the active folders in the current folder
          $query = qq{SELECT name, id, groupr, otherr, groupid, userid
                      FROM message 
                      WHERE folder='Y' AND folderid=$folderid AND open='Y' AND folderid!=id
                      ORDER BY name};
          $sth = $dbh->prepare ($query);
          $sth->execute ();
          $folder = '' if $folder eq $root_folder_name;
          my ($childfolder, $childfolderid, $folder_groupr, $folder_otherr, $folder_groupid, $folder_userid);
          while ( @ary = $sth->fetchrow_array () )
          {
            my ($childfolder, $childfolderid, $folder_groupr, $folder_otherr, $folder_groupid,
                $folder_userid) = @ary;
            my $folder_group_member = 'N'; # set default membership to N
            if ( defined($users_groups{$folder_groupid}) )
            {
              $folder_group_member = 'Y' ;
            }
            $folder_group_member = 'Y' if $name eq 'root'; # root overrides folder membership
            
            my $path = "$folder/$childfolder";
            $path =~ s/^\///;
            my $linkpath = $path;
            $linkpath =~ s/\ /%20/g;
            my $printed_flag = 'N'; # set printed_flag to 'N'
            if ( $folder_group_member eq 'Y' || $folder_otherr eq 'Y' || $name eq 'root' )
            {
              unless ( ($childfolder =~ /^\./ || $childfolder =~ /^~\./)
                     && $folder_group_member eq 'N'
                     || ( $our_userid != $folder_userid
                          && $folder_groupr eq 'N'
                          && $name ne 'root' ) )
              {
                $folder_array[$folder_count] = a({-href=>"$cgi_url?folder=$linkpath&expand=$expand",
                         -title=>"open folder, '$childfolder'"},
                      "<img src=\"/icons/crystal/16x16/filesystems/folder.png\" width=16".
                      "height=16 border=0 alt=\"[$childfolder]\">$childfolder");
                $printed_flag = 'Y'; # yes, we printed the folder
              }
            }
            else 
            {
              unless ($childfolder =~ /^\./ || $childfolder =~ /^~\./)
              {
                # $folder_array[$folder_count] .= qq{<img src="/icons/small/dir.gif" width=16}.
                $folder_array[$folder_count] .= qq{<img src="/icons/crystal/16x16/filesystems/folder.png"}.
                      qq{ width=16 height=16 border=0 alt="$childfolder">$childfolder};
                $printed_flag = 'Y'; # yes, we printed the folder
              }
            }
            my $folder_session_message_count = 0;
            if ( $folder_group_member eq 'Y' || $folder_otherr eq 'Y' || $name eq 'root' )
            {
              unless ( ($childfolder =~ /^\./ || $childfolder =~ /^~\./)
                      && $folder_group_member eq 'N' 
                     || ( $our_userid != $folder_userid
                          && $folder_groupr eq 'N'
                          && $name ne 'root' ) )
              {
                $last_post_time = 0;
                ($childcount, $todays_children, $weeks_children, $sessions_children,
                    $last_post_time) = countchildren($childfolderid);
                my $foldercount = countfolders($childfolderid);
                $folder_session_message_count = quick_folder_count($childfolderid) || 0;
                if ($last_post_time)
                {
                  $folder_array[$folder_count]
                               .= " ($childcount/<font color=#3333dd><b>$todays_children</b></font>/";
                  $folder_array[$folder_count] .= "<font color=#EE0000><b>" if $sessions_children > 0;
                  $folder_array[$folder_count] .= "$sessions_children";
                  $folder_array[$folder_count] .= "</b></font>" if $sessions_children > 0;
                  $folder_array[$folder_count] .= ")";
                }
                if ( $foldercount ) 
                {
                  $folder_array[$folder_count] .= " <font color=#009900>($foldercount folder";
                  $folder_array[$folder_count] .= "s" if $foldercount>1;
                  $folder_array[$folder_count] .= " / <font color=#997700><b>$folder_session_message_count".
                                                  "</b></font>" if $folder_session_message_count > 0;
                  $folder_array[$folder_count] .= ")</font>";
                }
              }
            }
            # only print <br> if we've printed a folder name
            $folder_array[$folder_count] .= br if $printed_flag eq 'Y';
            $folder_count ++ if $printed_flag eq 'Y'; # only print <br> if we've printed a folder name
          }
          $sth->finish ();

          # display the CLOSED folders in the current folder
          $query = qq{SELECT name, id, groupr, otherr, groupid, userid 
                      FROM message 
                      WHERE folder='Y' AND folderid=$folderid AND open='N' AND folderid!=id
                      ORDER BY name};
          $sth = $dbh->prepare ($query);
          $sth->execute ();
          $folder = '' if $folder eq $root_folder_name;
          while ( @ary = $sth->fetchrow_array () ) 
          {
            my ($childfolder, $childfolderid, $folder_groupr, $folder_otherr, $folder_groupid,
             $folder_userid) = @ary;
            my $folder_group_member = 'N'; # set default membership to N
            if ( defined($users_groups{$folder_groupid}) )
            {
              $folder_group_member = 'Y' ;
            }
            $folder_group_member = 'Y' if $name eq 'root'; # root overrides folder membership
            
            my $path = "$folder/$childfolder";
            $path =~ s/^\///;
            my $linkpath = $path;
            $linkpath =~ s/\ /%20/g;
            my $printed_flag = 'N'; # set printed_flag to 'N'
            my $foldercount;
            if ( $folder_group_member eq 'Y' || $folder_otherr eq 'Y' || $name eq 'root' ) 
            {
              unless ( ($childfolder =~ /^\./ || $childfolder =~ /^~\./)
                     && $folder_group_member eq 'N' 
                     || ( $our_userid != $folder_userid
                          && $folder_groupr eq 'N'
                          && $name ne 'root' ) )
              {
                $folder_array[$folder_count] .= a({-href=>"$cgi_url?folder=$linkpath&expand=$expand"},
                                        "<img src=\"/icons/crystal/16x16/filesystems/folder_locked.png\" ".
                                        "width=16 height=16 border=0 alt=\"$childfolder\">$childfolder");
                $printed_flag = 'Y'; # yes, we printed the folder
              }
            }
            else 
            {
              if ($childfolder =~ /^\w/) 
              {
                $folder_array[$folder_count]
                     .= qq{<img src="/icons/crystal/16x16/filesystems/folder_locked.png" width=16 }.
                        qq{height=16 border=0 alt="$childfolder">$childfolder};
                $printed_flag = 'Y'; # yes, we printed the folder
              }
            }
            if ( $folder_group_member eq 'Y' || $folder_otherr eq 'Y' || $name eq 'root' ) 
            {
              unless ( ($childfolder =~ /^\./ || $childfolder =~ /^~\./)
                      && $folder_group_member eq 'N' 
                     || ( $our_userid != $folder_userid && $folder_groupr eq 'N' ) ) 
              {
                $last_post_time = 0;
                ($childcount, $todays_children, $weeks_children, $sessions_children,
                 $last_post_time) = countchildren($childfolderid);
                $foldercount = countfolders($childfolderid);
                if ($last_post_time) 
                {
                  $folder_array[$folder_count] .= " ($childcount/<font color=#3333ff><b>$todays_children</b></font>/";
                  $folder_array[$folder_count] .= "<font color=#EE0000><b>" if $sessions_children > 0;
                  $folder_array[$folder_count] .= "$sessions_children";
                  $folder_array[$folder_count] .= "</b></font>" if $sessions_children > 0;
                  $folder_array[$folder_count] .= ")";
                }
                if ( $foldercount ) 
                {
                  $folder_array[$folder_count] .= " <font color=#009900>($foldercount folder";
                  $folder_array[$folder_count] .= "s" if $foldercount>1;
                  $folder_array[$folder_count] .= ")</font>";
                }
              }
            }
# only print <br> if we've printed a folder name
            $folder_array[$folder_count] .= " [CLOSED]<br>" if $printed_flag eq 'Y';
            $folder_count ++ if $printed_flag eq 'Y'; # only print <br> if we've printed a folder name
          }
          $sth->finish ();
        }

        my $col1;
        if ($folder_count < 13)
        {
           $col1 = 6;
        }
        else
        {
          $col1 = ( ( $folder_count % 2) + $folder_count ) / 2;
        }

        my $recount = 0; # counter for recounting folders as we print them
        foreach my $folder_line (@folder_array)
        {
          last if $recount >= $folder_count;
          print $folder_line;
          $recount ++;
          print qq{</td><td valign="top">} if $recount == $col1;
        }
        $recount = 0; # force $recount back to 0
        $folder_count = 0; # force $folder_count to 0
        
        print qq{<input type="hidden" name="expand" value="$expand">};
        print end_form();
        print qq{</td>};
        
## print calendar -- possible future feature.
#    if($current_system_time)
#    {
#      print qq{<td>};
#      my $cal = `cal`;
#      chomp($cal);
#      my $today_cal = `date +%e`;
#      chomp($today_cal);
#      $cal =~ s/\b$today_cal\b/<font style="background-color: #FFFF44; font-weight: bold; ">$today_cal<\/font>/;
#      print qq{<table border="1"><tr><td>};
#      print qq{<pre><font style="font-size: 140%; ">$cal</font></pre>};
#      print qq{</td></tr></table>};
#      print qq{</td>};
#    }
## end of calendar printing routine

        print qq{</td></tr></table>};

        # Quick stats for root user (only display in root folder
        if ( $folderid == 1 && $name eq 'root' ) 
        {
          print qq{<span style="font-size: 85%; ">};
          # set up table to put tables in
          print qq{<table border=0><tr><td valign="top">};

          # set up user summary table
          print qq{<table border=1 bgcolor="$fresh_message_background"><tr><th></th><th>messages</th><th>users</th></tr>};

          # last 24 hours
          print qq{<tr><td>last 24 hours</td>};

          # messages in last 24 hours
          $query = qq{SELECT count(*) FROM message
                      WHERE folder='N'
                          AND open='Y'
                          AND time > date_format(date_sub(now(), INTERVAL 1 day), "%Y%m%d%H%i%s")};
          $sth = $dbh->prepare ($query);
          $sth->execute ();
          my ($last_24) = $sth->fetchrow_array () || 0;
          $sth->finish ();
          print "<td>$last_24</td>";

          # users in last 24 hours
          $query = qq{SELECT count(*) FROM user
                    WHERE timestamp > date_format(date_sub(now(), INTERVAL 1 day), "%Y%m%d%H%i%s")};
          $sth = $dbh->prepare ($query);
          $sth->execute ();
          ($last_24) = $sth->fetchrow_array () || 0;
          print "<td>$last_24</td>";

          print "</tr>";

          # last 7 days
          print qq{<tr><td>last 7 days</td>};

          # Messages in last 7 days
          $query = qq{SELECT count(*) FROM message
                      WHERE folder='N'
                          AND open='Y'
                          AND time > date_format(date_sub(now(), INTERVAL 7 day), "%Y%m%d%H%i%s")};
          $sth = $dbh->prepare ($query);
          $sth->execute ();
          my ($last_7) = $sth->fetchrow_array () || 0;
          $sth->finish ();
          print "<td>$last_7</td>";

          # users in last 7 days
          $query = qq{SELECT count(*) FROM user
                    WHERE timestamp > date_format(date_sub(now(), INTERVAL 7 day), "%Y%m%d%H%i%s")};
          $sth = $dbh->prepare ($query);
          $sth->execute ();
          ($last_7) = $sth->fetchrow_array () || 0;
          print "<td>$last_7</td>";

          # totals
          print qq{<tr><td>total</td>};

          # Total Messages
          $query = qq{SELECT count(*) FROM message
                      WHERE folder='N' AND open='Y'};
          $sth = $dbh->prepare ($query);
          $sth->execute ();
          my ($total) = $sth->fetchrow_array () || 0;
          $sth->finish ();
          print "<td>$total</td>";

          # total users
          $query = qq{SELECT count(*) FROM user};
          $sth = $dbh->prepare ($query);
          $sth->execute ();
          ($total) = $sth->fetchrow_array () || 0;
          print "<td>$total</td>";

          # close user sumary table box
          print "</tr>";
          print "</table>";

          # close user summary cell, open file summary cell
          print qq{</td><td valign="top">};

          # set up file summary table
          print qq{<table border=1 bgcolor="$fresh_message_background"><tr><th></th><th>files</th><th>size</th></tr>};

          # gather attachment information
          $query = qq{SELECT id FROM attachment};
          $sth = $dbh->prepare ($query);
          my $db_files = int ($sth->execute () );
          my $total_file_size = 0; # set initial value to 0
          my ($attachment, $local_file_name, $file_size, $in_place_files);
          while ( @ary = $sth->fetchrow_array () )
          {
            ($attachment) = @ary;
            $local_file_name = sprintf("0%010d", $attachment);
            $file_size = 0; # set initial value to 0 for each file
            $file_size = -s "$home_dir/re_files/$local_file_name" || 0;
            $total_file_size += $file_size;
            $in_place_files ++ if ( -e "$home_dir/re_files/$local_file_name" );
          }
          $sth->finish ();
          print qq{<tr><td>files in db</td><td>$db_files</td><td> - </td></tr>};
# normalize file sizes to KB/MB/GB/TB
          $total_file_size = human_readable($total_file_size);
          print qq{<tr><td>files in place</td><td>$in_place_files</td><td>$total_file_size</td></tr>};
          print qq{<tr><td align="center" colspan="3">[ <a href="$cgi_url?op=}.
                qq{attachment%20administration">attachment administration</a> ]</td></tr>};

          # close file sumary table box
          print "</tr>";
          print "</table>";


          # close sumary table box
          print "</tr>";
          print "</table>";

          print "</tr>";
          print "</table>";

          print qq{</span>};
        }
      
        print qq{<table border=0><tr><td>};
        print qq{</td></tr></table>};
        # Surround the Recently online and folder summary with a table to make it not "get lost."
        print qq{<table border=1 align="left" bgcolor="$fresh_message_background"><tr><td>};
        print qq{<font class="navigation" style="color: #666666; font-weight: bold; ">};
        print "Recently online: $currently_online</font>",br if $name;
        # Printer folder summary
        if ( $groupmember eq 'Y' 
              || $otherr eq 'Y' 
              || $name eq 'root' ) 
        {
          ($childcount, $todays_children, $weeks_children, $sessions_children,
           $last_post_time) = countchildren($folderid);
          print qq{<font class="navigation">};
          print "$childcount total" if $childcount;
          print ", " if $childcount && $todays_children;
          print "$todays_children today \@ $last_post_time" if $todays_children;
          print ", " if $weeks_children > $todays_children;
          print "$weeks_children this week" if $weeks_children > $todays_children;
          print qq{ <font style="background-color: $fresh_title_background; font-weight: bold; "> }.
                "&nbsp; session: $sessions_children &nbsp;</font>" if $sessions_children;
          print "</font>";
        }
        print qq{</td></tr></table>};
        quick_jump_menu(1);
        print br;
        # print qq{</td></tr></table>};
      }
    }
    else
    {
      print header(-cookie=>[$cookie],
                 -cache_control=> "no-cache",
                 -cache_control=> "max-age=0") if $op ne 'login';

      print start_html(-meta=>{'MSSmartTagsPreventParsing'=>'True'},
                       -expires=> "Mon, 26 Jul 1997 05:00:00 GMT",
                       -cache_control=> "post-check=0,pre-check=0",
                       -pragma=> "no-cache",
                       -style=>{-src=>'/re_default.css'}),"\n";
    }

    # print folder description
    if ( $contents && (($otherr eq 'Y' || $our_userid == $ownerid) 
        || ($groupmember eq 'Y' && $groupr eq 'Y') || $name eq 'root') && $op ne 'isolate') 
    {
      print_contents($contents);
    }
  }

  unless ( $op eq 'clean output' )
  {
    # print br;
    # print $folder_query,br; # for debugging purposes, otherwise, it should be commented out.

    $url_folder = $folder || '';
    $url_folder =~ s/\ /%20/g;

    if ($otherr eq 'Y' 
        || ($groupmember eq 'Y' && $groupr eq 'Y') 
        || $username eq 'root'  
        || $our_userid == $ownerid ) 
    {
      # calculate number of pages and put us on specific page if requested
      my $sql_query = qq{SELECT message.id
                         FROM message, threads  
                         WHERE folderid=$folderid AND parentid=$folderid AND folder='N'
                            AND open='Y'
                            AND threads.id = message.id
                         ORDER BY threads.time DESC};
      my $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
      my $threads = int ( $sth->execute() );
      $pages = ceil($threads/$thread_limit);
      $page = param('page') || 1;
      $page = $pages if $page > $pages;
      #  $page = 1 if $page < 1;
      # find page that requested thread is on
      my $threadid;
      if ($op eq 'expand' && $expandmessage) 
      {
        my $i = 0; # reset counter
        while ( my @ary = $sth->fetchrow_array () ) 
        {
          $i++; # incriment counter
          ($threadid) = @ary;
          last if $threadid == $expandmessage;
        }
        $page = ceil($i/$thread_limit);
      }
      $sth->finish ();
      # $url_folder = $linkfolder;

# print links for different thread styles
      unless ( $op eq 'isolate' )
      {
        print qq{<table align=right style="background-color: $fresh_title_background; "><tr>}.
              qq{<td class="navigation">};
        print qq{Change thread style: };
        print qq{ <a href="$cgi_url?folder=$url_folder&expand=1&page=$page" }.
              qq{title="Normal: ``fresh'' and unread messages expanded">} unless $expand==1;
        print qq{1</a> };
        print qq{<a href="$cgi_url?folder=$url_folder&expand=2&page=$page" }.
              qq{title="Compressed: only unread messages expanded">} unless $expand==2;
        print qq{2</a> };
        print qq{<a href="$cgi_url?folder=$url_folder&expand=3&page=$page" }.
              qq{title="Expanded: ALL messages expanded">} unless $expand==3;
        print qq{3</a> };
        print qq{<a href="$cgi_url?folder=$url_folder&expand=4&page=$page" }.
              qq{title="Heavy compression: threads compressed to 1 line, w/unread messages expanded">} unless $expand==4;
        print qq{4</a>};
        print qq{</td></tr></table>};
      }

      print qq{<p class="navigation">}.
            qq{[&nbsp;<a href="$cgi_url?folder=$url_folder&expand=3&page=$page" }.
            qq{title='"Expand" all messages to make all the contents of all }.
            qq{the messages visable.'>}.
            qq{Expand&nbsp;all&nbsp;messages</a>&nbsp;]} unless ($expand==3 || $op eq 'isolate');
      print br if ( $expand==5 );
      print qq{&nbsp;[&nbsp;<a href="$cgi_url?folder=$url_folder&expand=5&page=$page" }.
            qq{title='"Expand" messages added in the last 7 days.'>}.
            qq{Expand&nbsp;messages&nbsp;for&nbsp;week</a>&nbsp;]},br
                  unless ($expand==3 || $expand==5 || $op eq 'isolate');
      print qq{<p class="navigation">}.
            qq{[ <a href="$cgi_url?folder=$url_folder&page=$page&}.
            qq{expand=$expand" title='"Collapse" old messages so that only }.
            qq{the new messages are "expanded."'>Collapse old messages</a> ]},br
                if $op eq 'expand';
      print qq{<p class="navigation">[ <a href="$cgi_url?folder=$url_folder&}.
            qq{page=$page&expand=$thread_style">Collapse old messages</a> ]},br
                if $expand==3;

      # page shifting code...
      my $first_display_page = 1; # set default first page
      my $last_display_page = $pages; # set default last page
      if ( $pages > 20 )
      {
        $last_display_page = 20;
        if ( $page > 10 )
        {
          $last_display_page = $page + 9;
          $first_display_page = $page - 10;
          if ($last_display_page > $pages )
          {
            $last_display_page = $pages;
            $first_display_page = $pages - 19;
          }
        }
      }

# print page navigation links (prev, next, 1,2,3, etc)
      my ($last_page, $next_page);
      if ( $page != -1 && $op ne 'isolate' ) 
      {
        $last_page = $page - 1;
        print qq{<a href="$cgi_url?folder=$url_folder&page=$last_page&expand=$expand" }.
              qq{ title="go to previous page (page $last_page)">&lt;&lt;}.
              qq{</a>&nbsp;&nbsp;}
              if $page > 1;
        for ($i=$first_display_page; $i<=$last_display_page; $i++) 
        {
          print qq{<a href="$cgi_url?folder=$url_folder&page=$i&expand=$expand" }.
                qq{title="jump to page $i of $pages">} if $i != $page;
          print qq{<b><font style="background-color: $fresh_title_background; }.
                qq{padding-left: 4px; padding-right: 4px; ">}
                if ( $i == $page && $pages != 1 );
          print qq{$i} if $pages != 1;
          print qq{</font></b>} if ( $i == $page && $pages != 1 );
          print qq{</a>} if $i != $page;
          print qq{&nbsp;&nbsp;};
        }
        $next_page = $page + 1;
        print qq{<a href="$cgi_url?folder=$url_folder&page=$next_page&expand=$expand" }.
              qq{ title="go to next page (page $next_page)">&gt;&gt;</a>}
              if $page < $pages;
        print qq{&nbsp;&nbsp;&nbsp;<a href="$cgi_url?folder=$url_folder&page=-1&expand=$expand" }.
              qq{title="show all messages on one page">}.
              qq{[one, big page]</a>} if $pages > 1;
      }
      else
      {
        print qq{[ <a href="$cgi_url?folder=$url_folder&expand=$expand" }.
              qq{ title="return to normal view of this folder">Return to $folder</a> ]}
      }

      $folder_session_age = folder_session_age($folderid);
      if ($op eq 'isolate')
      {
        $op_status = "isolate top"; # set isolate top flag
        threadchildren($expandmessage, "$nth_level_thread_order", 0, 3, '');
      }
      elsif ( $op eq 'publish' )
      {
        print header(-cookie=>[$cookie],
                   -cache_control=> "no-cache",
                   -cache_control=> "max-age=0") if $op ne 'login';

        print start_html(-meta=>{'MSSmartTagsPreventParsing'=>'True'},
                         -expires=> "Mon, 26 Jul 1997 05:00:00 GMT",
                         -cache_control=> "post-check=0,pre-check=0",
                         -pragma=> "no-cache",
                         -style=>{-src=>'/re_default.css'}),"\n";
        $expand = 3;
        threadchildren($folderid, qq{$first_level_thread_order}, 0, 3, '');
      }
      else
      {
        if ( $page > 2 )
        {
          threadchildren($folderid, qq{$first_level_thread_order}, 0, 3, '');
        } else {
          threadchildren($folderid, qq{$first_level_thread_order}, 0, $expand, '');
        }
      }

      if ( $page != -1 && $op ne 'isolate') 
      {
        print qq{<p class="navigation">};
        print qq{<a href="$cgi_url?folder=$url_folder&page=$last_page&expand=$expand" }.
              qq{ title="go to previous page (page $last_page)">&lt;&lt;}.
              qq{</a>&nbsp;&nbsp;}
              if $page > 1;
        for ($i=$first_display_page; $i<=$last_display_page; $i++) 
        {
          print qq{<a href="$cgi_url?folder=$url_folder&page=$i&expand=$expand" }.
                qq{title="jump to page $i of $pages">} if $i != $page;
          print qq{<b><font style="background-color: $fresh_title_background; }.
                qq{padding-left: 4px; padding-right: 4px; ">}
                if ( $i == $page && $pages != 1 );
          print qq{$i} if $pages != 1;
          print qq{</font></b>} if $i == $page;
          print qq{</a>} if $i != $page;
          print qq{&nbsp;&nbsp;};
        }
        $next_page = $page + 1;
        print qq{<a href="$cgi_url?folder=$url_folder&page=$next_page&expand=$expand" }.
              qq{ title="go to next page (page $next_page)">&gt;&gt;</a>}
              if $page < $pages;
        print qq{&nbsp;&nbsp;&nbsp;<a href="$cgi_url?folder=$url_folder&page=-1&expand=$expand" }.
              qq{title="show all messages on one page">}.
              qq{[one, big page]</a>} if $pages > 1;
      }
      else
      {
        print qq{[ <a href="$cgi_url?folder=$url_folder&expand=$expand" }.
              qq{ title="return to normal view of this folder">Return to $folder</a> ]}
      }
      print qq{</p>};
    }

    print br,$page_footer if $page_footer;
    print qq{</center>};
    print qq{<p style="text-align: right; font-size:60%; margin-top: 0em; }.
          qq{margin-bottom: 0em;">v $version</p>};
    print qq{</body>\n\n};
    print qq{<head>\n}.
          qq{<meta HTTP-EQUIV="Expires" CONTENT="Mon, 26 Jul 1997 05:00:00 GMT">\n}.
          qq{<meta HTTP-EQUIV="Cache-Control" CONTENT="no-cache">\n}.
          qq{<meta HTTP-EQUIV="Cache-Control" CONTENT="post-check=0,pre-check=0">\n}.
          qq{<meta HTTP-EQUIV="Cache-Control" CONTENT="max-age=0">\n}.
          qq{<meta HTTP-EQUIV="Pragma" CONTENT="no-cache">\n}.
          qq{</head>\n\n};
    print end_html();
    $sth->finish ();
  }

  $sth->finish ();
  $dbh->disconnect ();
  foreach my $key (keys %users_groups)
  {
    $users_groups{$key} = undef();
  }
  while (@folder_array) # flush folders as last step (mod_perl will keep these)
  {
    pop(@folder_array);
  }
  $name = ''; # make sure name is cleared on exit.

# $t1 = new Benchmark;
# my $td = timediff($t1, $t0);
# my $tf = timestr($td);
# warn qq{Benchmark time ($foldername): $tf\n};
}
$sth->finish ();
$dbh->disconnect ();

sub threadchildren 
{
  my($parent, $order, $root_message_id, $thread_style, $parent_title) = @_;
  my ($messagename, $datetime, $URI, $message_username, $messageid, $attachment);

  # thread_style -- how to display the message thread
  #   1 = "normal" threading - calapse everything older than "fresh"
  #   2 = "user enhanced" threading - calapse everyting older than users last session
  #   3 = "expanded" threading - expand the whole thread, but only the thread starting
  #       at $root_message_id
  #    4 = "compressed" - show thread, thread "owner" and thread summary only with aging
#     5 = "this week" - calapse messages older than 7 days (thread styel 1 enhanced for week)
  $thread_style = 1 unless defined($thread_style);

  # if $root_message_id is not passed to us, we'll set a "folder" flag
  # if the "folder" flag is set, we'll assign a $root_message_id on the first level messages
  my $folder_flag = 1 unless $root_message_id>0;

  my $offset_threads = $thread_limit*($page-1);

  unless ( defined($limit) || $page == -1 )
  {
    $limit = qq{LIMIT $offset_threads,$thread_limit}    # if $limit is defined, create LIMIT query string
  }
  else
  {
    $limit = '';
  }

  my $sql_query;
  if ( $parent == $folderid )
  {
    $sql_query = qq{
          SELECT message.name, date_format(message.time, '%e.%b.%Y %h:%i %p'), URI, user.name, message.id,
            ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(message.time) ) as age, attachment.id
          FROM (message, user, threads )
                LEFT JOIN attachment ON attachment.message_id=message.id
          WHERE parentid=$parent
                AND folderid=$folderid
                AND threads.id = message.id
                AND userid=user.id
                AND folder='N' 
                AND open='Y'
                
          GROUP BY message.id
          ORDER BY threads.time DESC
          $limit
          };
  }
  elsif ($op_status eq 'isolate top') # if this is a request for an isolated thread, start at the top of the thread
  {
    $sql_query = qq{
          SELECT message.name, date_format(message.time, '%e.%b.%Y %h:%i %p'), URI, user.name, message.id,
            ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(time) ) as age, attachment.id
          FROM (message, user )
                LEFT JOIN attachment ON attachment.message_id=message.id
          WHERE message.id=$parent
                AND folderid=$folderid
                AND userid=user.id
                AND folder='N' 
                AND open='Y'
                
          GROUP BY message.id
          ORDER BY message.id $order
          $limit
          };
    $op_status = 'inside'; #set $op_status to arbitary, non-"isolate top" value
  }
  else
  {
    $sql_query = qq{
          SELECT message.name, date_format(message.time, '%e.%b.%Y %h:%i %p'), URI, user.name, message.id,
            ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(time) ) as age, attachment.id
          FROM ( message, user )
                LEFT JOIN attachment ON attachment.message_id=message.id
          WHERE parentid=$parent
                AND folderid=$folderid
                AND userid=user.id
                AND folder='N' 
                AND open='Y'
                
          GROUP BY message.id
          ORDER BY message.id $order
          $limit
          };
  }
  my $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
  my $rv = int ( $sth->execute() );
  if ($rv > 0) 
  {
    while ( my @ary = $sth->fetchrow_array () ) 
    {
      my ($messagename, $datetime, $URI, $message_username, $messageid, $age, $attachment) = @ary;
      
      #slap empty message names
      $messagename = "[no subject]" unless $messagename;
      $messagename =~ s#http://">##g;

      # HTML tag filter based on @approved_tags list at top
      $_ = $messagename;
      my @tags = m#<.*?>#g;
      my ($test_tag, $approved_tag);
      EACH_TAG: foreach my $tag (@tags) 
      {
        $test_tag = $tag;
        $test_tag =~ tr/A-Z/a-z/;
        $test_tag =~ s#</*\s*(\S*).*\s*/*>#$1#;
        TAG_TEST: foreach $approved_tag (@approved_tags) 
        {
          next EACH_TAG if $approved_tag eq $test_tag; # tag good, next
        }
        $messagename =~ s#$tag##g; # remove the tag if not in approved list
      }

      my $trimmed_messagename = $messagename; # we'll use the "trimmed messagename" 
      $trimmed_messagename =~ s/^RE:\ //;     # for dropping interstitial message title
                                           # blocks.
      
      $root_message_id = $messageid if $folder_flag; # > 0; 
          # if $folder_flag is set, grab a $root_message_id which will be
          # passed on to our children.

      # freshness indicator
      $message_background = $day_old_message_background if $age > $warm_message_time;
      $message_background = $warm_message_background if $age < $warm_message_time;
      $message_background = $fresh_message_background if $age < $fresh_message_time;

      my $title_background = $day_old_title_background if $age > $warm_message_time;
      $title_background = $warm_title_background if $age < $warm_message_time;
      $title_background = $fresh_title_background if $age < $fresh_message_time;

      print qq{\n<ul>\n} unless ($thread_style == 4 && $expandmessage!=$messageid);

      # expand for level 4 expansion
      if ($thread_style == 4 && $messageid != $expandmessage )
      {
        # print qq{<ul>};
        my ($childcount, $todays_children, $weeks_children, $sessions_children) = countthread($messageid);
        $sessions_children=0 unless $sessions_children;


        my $border;
        if ($sessions_children > 0 || $age < $folder_session_age)
        {
          print qq{\n<ul>\n};
          print qq{<a name="$messageid">\n};  # name tag for intra-page linking
          if ( $messageid == $searchresult ) { $border = 1; } # set border visible on
                                                              # search result
          else { $border = 0; } # otherwise, we'll have an invisable table border
          print "<table border=$border cellspacing=2>\n"; # start message table
          print "<tr>\n" # start message row for fresh or session messages (thread 1-3)
              if ( $age < $folder_session_age );
          if ( $age < $folder_session_age )
          {
            print "<td bgcolor='#EEEE00'>&nbsp;</td>\n" # print session flag if new since last
                if ($age < $folder_session_age);              # session
            print qq{<td bgcolor="$title_background">\n};
            print qq{<p class="body_text" style="background-color: $title_background; };
            print qq{border-color: $title_background; border-style: outset; };
            print qq{padding-top:1px; padding-bottom: 1px; };
            if ($root_message_id != $messageid) 
            {
              print qq{font-size: 80%; };
            }
            print qq{">};
            # highlight search terms in message name to make them easy finding
            my $display_messagename = $messagename;
            foreach $mark (@marks)
            {
              $display_messagename =~
                  s/\b($mark)\b/<font style="background-color: $fresh_title_background; ">$1<\/font>/gi;
            }
            print qq{<b>$display_messagename</b></p></td></tr\n>};
          }
          print "<tr>";
          print "<td bgcolor='#EE0000' rowspan=2>&nbsp;</td>\n" if ($age < $folder_session_age); # session flag
          if ( $age > $folder_session_age && $messageid != $expandmessage)
          {
            print qq{<td bgcolor=$title_background>\n};
            print qq{<p class="body_text" style="background-color: $title_background; };
            print qq{border-color: $title_background; border-style: outset; };
            print qq{padding-top:1px; padding-bottom: 1px; };
            if ($root_message_id != $messageid)
            {
              print qq{font-size: 80%; };
            }
            print qq{">};
            print qq{<a href="$cgi_url?op=expand&message=$root_message_id&}.
                  qq{expand=$expand#$messageid" title="Expand this thread">};
            print qq{<b>$messagename</b></a>&nbsp;- };
          }
          else 
          {
            print qq{<td bgcolor=$message_background>}.
                  qq{<p class="body_text" }.
                  qq{style="background-color: $message_background; }.
                  qq{border-color: $message_background; ">};
          }
          print qq{<font style="font-size: 80%; "><b>[by&nbsp;};
          print qq{$message_username};
          $datetime =~ s/\ /\&nbsp;/g;
          print qq{]</b>&nbsp;-&nbsp;posted:&nbsp;$datetime};
          print qq{</font>};

          my $message_expanded = 'N'; # reset expanded message flag to 'N'
                         # this is used to determine if we need to print the
                         # "expand thread" link
          my ($linkname, $contents, $attachment_name, $message_userid);
          if ( $age < $folder_session_age )
          {
# mangle message title to keep it from screwing things up
            my $mangled_messagename = $messagename;
            $mangled_messagename =~ s/"/&quot;/g;
            $mangled_messagename =~ s/'/\\'/g;
            $mangled_messagename =~ s/<.*?>//g;
            $mangled_messagename =~ s/>/&gt;/g;
            $mangled_messagename =~ s/</&lt;/g;
            print qq{<font style="font-size: 80%; ">};
            # print "dura-link" for durable links
            print qq{&nbsp;[<a href="$cgi_url?op=expand&message=$root_message_id#$messageid" }.
                  qq{title="copy this link to create a durable link to }.
                  qq{this message, '$mangled_messagename'">dura-link</a>]};
            # print "isolate-link" for isolation links
            print qq{&nbsp;<tt>[<a href="$cgi_url?op=isolate&message=$root_message_id" }.
                  qq{title="click to isolate thread for printing, }.
                  qq{'$mangled_messagename'">i</a>]</tt>} unless $op eq 'isolate';
            print qq{</font>\n},br,"\n";

          my $sql_query = qq{
                SELECT linkname, contents, attachment.filename, userid
                FROM ( message, user )
                      LEFT JOIN attachment ON attachment.message_id=message.id
                WHERE message.id=$messageid
                };
          my $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
          $sth->execute();
          ($linkname, $contents, $attachment_name, $message_userid) = $sth->fetchrow_array ();

            print_contents($contents);
            if ($URI)
            {
              $URI = escapeHTML("$URI");
              print qq{<a href="$URI" target="_blank">};
              print qq{<img src="/icons/crystal/16x16/filesystems/network.png" width="16" height="16" }.
                    qq{align="middle" }.
                    qq{title="visit };
              $linkname ? print "$linkname" : print "$URI";
              print qq{" border="0"> };
              $linkname ? print "$linkname" : print "$URI";
              print "</a>",br,"\n";
            }
            my ($local_file_name, @file_info, $attachment_size, $internal_attachment_name);
            if ($attachment) 
            {
              $local_file_name = sprintf("0%010d", $attachment);
              @file_info = (stat  "$home_dir/re_files/$local_file_name");
              $attachment_size = $file_info[7];

              $attachment_size = human_readable($attachment_size);
              $internal_attachment_name = escapeHTML("$attachment_name");
              $internal_attachment_name =~ s/\ /%20/g;
            }
            print qq{<a href="$cgi_url/$internal_attachment_name?op=norefresh&download=$attachment" }.
                  qq{title="download '$attachment_name'">}.
                  qq{<img src="/icons/crystal/22x22/actions/attach.png" border=0> $attachment_name</a> }.
                  qq{($attachment_size)},br if $attachment;

            $message_expanded = 'Y'; # set this flag is message was expanded
                                     # this is used to determine if we need to print the
                                     # "expand thread" link
          }
          if ( $message_expanded eq 'N' )
          {
            print qq{<img src="/icons/crystal/22x22/actions/attach.png" border=0 }.
                  qq{title='This message has an attachment, "$attachment_name"'>} if $attachment;
            if ($URI)
            {
              $URI = escapeHTML("$URI");
              print qq{<img src="/icons/crystal/16x16/filesystems/network.png" width="16" height="16" }.
                    qq{title="user submitted link: };
                $linkname ? print "$linkname" : print "$URI";
              print qq{">};
            }
          }
          else
          {
          print qq{[ <a href="$cgi_url?folder=$url_folder&op=new%20entry&id=$messageid&expand=$expand" }.
                qq{title="Post reply to this message">Reply</a> ] }
                if ( ($otherw eq 'Y' 
                      || ($groupmember eq 'Y' 
                            && $groupw eq 'Y')
                        || $our_userid == $ownerid) 
                      && $open eq 'Y' 
                      && $our_userid>0 );

          print qq{[ <a href="$cgi_url?folder=$url_folder&op=edit&id=$messageid&expand=$expand">Edit</a> ] } 
                if ( ($our_userid == $message_userid
                       && ( $groupmember eq 'Y' && $groupw eq 'Y' || $our_userid == $ownerid ) )
                   && $open eq 'Y' 
                   && $our_userid >= 1  # to keep 'Guest' from editing messages
                   && $age < ($edit_interval) );

          print qq{[ <a href="$cgi_url?folder=$url_folder&op=bury&id=$messageid&root=$root_message_id" }.
                qq{title="'Disapear' this message by marking it 'closed.'">DISAPEAR</a> ] } if $name eq 'root';
          }
          print "</td></tr></table>\n";

          # set $limit to "" so that there is no limit on children from this point on.
          $limit = qq{};
          $folder_session_age = folder_session_age($folderid);
          threadchildren($messageid, $nth_level_thread_order, $root_message_id, 2, $parent_title);
          print qq{</ul>\n};
          print qq{</ul>\n};
        }
        else
        {
          print "<table border=0 cellpadding=1 cellspacing=5>";
          print "<tr>";
          print qq{<td bgcolor=\"$title_background\">}.
                qq{<p class="body_text" style="border-style:outset; padding-top:1px; }.
                qq{ padding-bottom: 1px; background-color: $title_background; ">}.
                qq{<a href="$cgi_url?op=expand&message=$root_message_id&}.
                qq{expand=$expand#$root_message_id" title="Expand thread '$messagename'">}.
                qq{<b>$messagename</b></a> };
          print qq{<font style="font-size: 80%; "><b>};
          print "[by $message_username]";
          print qq{</b> - posted: $datetime};
          
          if ($childcount>0)
          {
            print qq{ (Replies: $childcount/<font color=#3333dd><b>$todays_children</b>}.
                  qq{</font>/};
            print qq{<font color=#EE0000><b>} if $sessions_children > 0;
            print qq{$sessions_children};
            print qq{</b></font>} if $sessions_children > 0;
            print qq{)</font>};
          }
          
          print "</p></td></tr></table>\n";
          # print qq{</ul>};
        }
      }
      # message threading for level 1-3
      else
      {
        print qq{<a name="$messageid">\n};  # name tag for intra-page linking
        my $border = 0;
        if ( $messageid == $searchresult ) { $border = 1; } # set border visible on search result
        else { $border = 0; } # otherwise, we'll have an invisable table border
        print "<table border=$border cellspacing=1>\n"; # start message table
        print "<tr>" # start message row for fresh or session messages (thread 1-3)
            if ( ( ($age < $fresh_message_time || $age < $folder_session_age) && $thread_style == 1 )
                || ( ($age < 7*86400 || $age < $folder_session_age) && $thread_style == 5 )
                || $thread_style == 3);
        if ( ( ( ($age < $fresh_message_time || $age < $folder_session_age) && $thread_style == 1 )
                || ( ($age < 7*86400 || $age < $folder_session_age) && $thread_style == 5 )
                || ( $age < $folder_session_age && $thread_style == 2 )
                || $thread_style == 3
                || $messageid == $expandmessage )
              && $trimmed_messagename ne $parent_title
              && $messagename ne $parent_title )
          {
            print "<td bgcolor='#EEEE00'>&nbsp;</td>\n" # print session flag if new since last
                if ($age < $folder_session_age);              # session
            print qq{<td bgcolor="$title_background">\n};
            print qq{<p class="body_text" style="background-color: $title_background; };
            print qq{border-color: $title_background; border-style: outset; };
            print qq{padding-top:1px; padding-bottom: 1px; };
            if ($root_message_id != $messageid) 
            {
              print qq{font-size: 80%; };
            }
            print qq{">};
            # highlight search terms in message name to make them easy finding
            my $display_messagename = $messagename;
            foreach $mark(@marks)
            {
              $display_messagename =~
                  s/\b($mark)\b/<font style="background-color: $fresh_title_background; ">$1<\/font>/gi;
            }
            print qq{<b>$display_messagename</b></p></td></tr>\n};
          }
        print "<tr>\n";
        print "<td bgcolor='#EE0000' rowspan=2>&nbsp;</td>\n" if ($age < $folder_session_age); # session flag
        if ( ( ($age > $fresh_message_time && $thread_style == 1)
               || ($age > 7*86400 && $thread_style == 5)
                                           || $thread_style == 2)
                  && $age > $folder_session_age
                  && $messageid != $expandmessage) 
        {
          print qq{<td bgcolor=$title_background>\n};
          print qq{<p class="body_text" style="background-color: $title_background; };
          print qq{border-color: $title_background; border-style: outset; };
          print qq{padding-top:1px; padding-bottom: 1px; };
          if ($root_message_id != $messageid) 
          {
            print qq{font-size: 80%; };
          }
          print qq{">};
          print qq{<a href="$cgi_url?op=expand&message=$root_message_id&}.
                qq{expand=$expand#$messageid" title="Expand this thread">};
          print qq{<b>$messagename</b></a>&nbsp;- };
        }
        else 
        {
          print qq{<td bgcolor=$message_background>\n<p class="body_text" }.
                qq{style="background-color: $message_background; border-color: $message_background; ">};
        }
      print qq{<font style="font-size: 80%; "><b>by&nbsp;};
      print qq{$message_username};
      $datetime =~ s/\ /\&nbsp;/g;
      print qq{</b>&nbsp;[&nbsp;posted:&nbsp;$datetime&nbsp;]};
      print qq{</font>};

      my $message_expanded = 'N'; # reset expanded message flag to 'N'
                     # this is used to determine if we need to print the
                     # "expand thread" link
      my ($linkname, $attachment_name, $local_file_name, @file_info, $attachment_size);
        if ( ( ( ($age < $fresh_message_time && $thread_style == 1) || ($age < 7*86400 && $thread_style == 5) )
                || $age < $folder_session_age) 
              || $thread_style == 3
              || $messageid == $expandmessage)
        {
# mangle message title to keep it from screwing things up
          my $mangled_messagename = $messagename;
          $mangled_messagename =~ s/"/&quot;/g;
          $mangled_messagename =~ s/'/\\'/g;
          $mangled_messagename =~ s/<.*?>//g;
          $mangled_messagename =~ s/>/&gt;/g;
          $mangled_messagename =~ s/</&lt;/g;
          # print "dura-link" for durable links
          print qq{<font style="font-size: 80%; ">};
          print qq{&nbsp;[<a href="$cgi_url?op=expand&message=$root_message_id#$messageid" }.
                qq{title="copy this link to create a durable link to }.
                qq{this message, '$mangled_messagename'">dura-link</a>]};
          print qq{</font>};
          # print "isolate-link" for isolation links
          print qq{<font style="font-size: 120%; ">};
          print qq{<tt>[<a href="$cgi_url?op=isolate&message=$root_message_id" }.
                qq{title="click to isolate thread for printing, }.
                qq{'$mangled_messagename'">I</a>]</tt>} unless $op eq 'isolate';
          print qq{</font>};
          print br,"\n";

          my $sql_query = qq{
                SELECT linkname, contents, attachment.filename, userid
                FROM ( message, user )
                      LEFT JOIN attachment ON attachment.message_id=message.id
                WHERE message.id=$messageid
                };
          my $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
          $sth->execute();
          ($linkname, $contents, $attachment_name, $message_userid) = $sth->fetchrow_array ();

          print_contents($contents);
          if ($URI) 
          {
            $URI = escapeHTML("$URI");
            print qq{<a href="$URI" target="_blank">};
            print qq{<img src="/icons/crystal/16x16/filesystems/network.png" width="16" height="16" }.
                  qq{align="middle" }.
                  qq{title="visit };
            $linkname ? print "$linkname" : print "$URI";
            print qq{" border="0">&nbsp;};
            $URI =~ s#(http\S{40})\S{4,}(\S{7})#$1....$2#ig; # trim long URLs to make them pretty
            $linkname ? print "$linkname" : print "$URI";
            print "</a>",br;
          }
          my ($internal_attachment_name);
          if ($attachment) 
          {
            $local_file_name = sprintf("0%010d", $attachment);
            @file_info = (stat  "$home_dir/re_files/$local_file_name");
            $attachment_size = $file_info[7];

            $attachment_size = human_readable($attachment_size);
            $internal_attachment_name = escapeHTML("$attachment_name");
            $internal_attachment_name =~ s/\ /%20/g;
          }
          print qq{<a href="$cgi_url/$internal_attachment_name?op=norefresh&download=$attachment" }.
                qq{title="download '$attachment_name'"><img src="/icons/crystal/22x22/actions/attach.png" }.
                qq{border=0> $attachment_name</a> ($attachment_size)},br if $attachment;

          $message_expanded = 'Y'; # set this flag is message was expanded
                                   # this is used to determine if we need to print the
                                   # "expand thread" link
        }
        if ( $message_expanded eq 'N' )
        {
          if ($attachment)
          {
            print qq{<img src="/icons/crystal/22x22/actions/attach.png" border=0 },
                  qq{title='This message has an attachment'>};
#          print qq{} if $attachment;
#          print qq{} if $attachment;
          }
          if ($URI)
          {
            $URI = escapeHTML("$URI");
            print qq{<img src="/icons/crystal/16x16/filesystems/network.png" width="16" height="16" }.
                  qq{title="user submitted link: };
            $linkname ? print "$linkname" : print "$URI";
            print qq{">};
          }
        }
        else
        {
          print qq{[ <a href="$cgi_url?folder=$url_folder&op=new%20entry&}.
                qq{id=$messageid&expand=$expand" }.
                qq{title="Post reply to this message">Reply</a> ] }
                if ( ($otherw eq 'Y' 
                      || ($groupmember eq 'Y' 
                            && $groupw eq 'Y')
                        || $our_userid == $ownerid) 
                      && $open eq 'Y' 
                      && $our_userid>0 );

          print qq{[ <a href="$cgi_url?folder=$url_folder&op=edit&}.
                qq{id=$messageid&expand=$expand">Edit</a> ] } 
                if ( ($our_userid == $message_userid
                       && ( $groupmember eq 'Y' && $groupw eq 'Y' || $our_userid == $ownerid ) )
                   && $open eq 'Y' 
                   && $our_userid >= 1  # to keep 'Guest' from editing messages
                   && $age < ($edit_interval) );

          print qq{[ <a href="$cgi_url?folder=$url_folder&op=bury&}.
                qq{id=$messageid&root=$root_message_id" }.
                qq{title="'Disapear' this message by marking it 'closed.'"}.
                qq{>DISAPEAR</a> ] }
                    if $name eq 'root';
        }
        print "</td></tr></table>\n";

        # set $limit to "" so that there is no limit on children from this point on.
        $limit = qq{};
        $folder_session_age = folder_session_age($folderid);
        if ( $op eq 'expand' && $messageid == $expandmessage)
        {
          threadchildren($messageid, "$nth_level_thread_order", $root_message_id, 3, $messagename);
        }
        else
        {
          threadchildren($messageid, "$nth_level_thread_order", $root_message_id, $thread_style, $messagename);
        }
      } # end thread_style 1 # 3 progression
      print "</ul>\n" unless ($thread_style == $expand && $expandmessage!=$messageid && $expand==4);
      }
    $sth->finish ();
    }
  $sth->finish ();
}

sub countchildren 
{
  my ($parent) = @_;
  my ($todays_children, $weeks_children, $sessions_children) = 0;
  my $sql_query = qq{ # get total messages in folder
                      SELECT count(*)
                      FROM message
                      WHERE folderid=$parent AND folder="N" AND open="Y"
                      };
  my $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
  $sth->execute();
  my ($childcount) = $sth->fetchrow_array ();
  $sth->finish ();
  $sql_query = qq{ # get all messages in last 24 hours
                  SELECT count(*)
                  FROM message
                  WHERE date_format(date_sub(now(), interval 1 day), '%Y%m%d%H%i%s') < time
                  AND folderid=$parent AND folder="N" AND open="Y"
                  };
  $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
  $sth->execute();
  ($todays_children) = $sth->fetchrow_array ();
  $sth->finish ();
  $sql_query = qq{ # children for last week (7 days)
                  SELECT count(*)
                  FROM message
                  WHERE date_format(date_sub(now(), interval 7 day), '%Y%m%d%H%i%s') < time
                  AND folderid=$parent AND folder="N" AND open="Y"
                  };
  $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
  $sth->execute();
  ($weeks_children) = $sth->fetchrow_array ();
  $sth->finish ();

  $folder_session_age = 0 if $our_userid == 0; # this is a hack.

  $folder_session_age = folder_session_age($parent);
  ($sessions_children) = countsessionchildren($parent);

  $sql_query = qq{
                  SELECT date_format(time, "%H:%i %p")
                  FROM message
                  WHERE folderid=$parent AND folder="N" AND open="Y"
                  ORDER BY time DESC
                  LIMIT 1
                  };
  $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
  $sth->execute();
  my ($last_post_format_time) = $sth->fetchrow_array ();
  $sth->finish ();
  return ($childcount, $todays_children, $weeks_children, $sessions_children,$last_post_format_time);
}

sub countsessionchildren 
{
  my ($parent) = @_;
  my ($sessions_children) = 0;

#  my $sql_query = qq{ # get the session children from the sessions table
#                      SELECT counter
#                      FROM sessions
#                      WHERE user=$our_userid 
#                        AND folder=$parent
#                      };
#  my $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
#  $sth->execute();
#  ($sessions_children) = $sth->fetchrow_array ();
#
#  warn ("folder - $parent: sessions_children: $sessions_children\n") if $parent == $folderid;
#  unless (defined($sessions_children))
#  {
    $folder_session_age = 0 if $our_userid == 0; # this is a hack.
       # I can't figure out why $session_age is not being set to 0 when our user
       # is 'Guest' ($our_userid == 0)

    my $sql_query = qq{ # get the session age time
                    SELECT date_format(date_sub(now(), INTERVAL $folder_session_age second), '%Y%m%d%H%i%s')
                    };
    my $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
    $sth->execute();
    my ($session_time) = $sth->fetchrow_array ();
    $sth->finish ();

    $sql_query = qq{ # new messages since last session
                    SELECT count(*)
                    FROM message
                    WHERE folderid=$parent
                      AND folder="N"  AND open="Y"
                      AND time > $session_time
                    };
#    my $sql_query = qq{ # new messages since last session
#                    SELECT count(*)
#                    FROM message, sessions
#                    WHERE message.folderid=$parent
#                      AND sessions.user=$our_userid
#                      AND sessions.folder=message.folderid
#                      AND message.folder="N"  AND message.open="Y"
#                      AND message.time >= sessions.timestamp
#                    };
    $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
    $sth->execute();
    ($sessions_children) = $sth->fetchrow_array () || 0;

## update the calculated value into the sessions table
#    $query = qq{UPDATE sessions
#                SET counter=$sessions_children, timestamp=timestamp
#                WHERE user=$our_userid AND folder=$parent
#                };
#    $sth = $dbh->prepare ($query);
#    $sth->execute ();
#    $sth->finish ();
#  }
  return ($sessions_children);
}

sub countthread 
{
  my ($parent) = @_;
  my ($todays_children, $weeks_children, $sessions_children) = 0;
  my $sql_query = qq{
                      SELECT id,
                      ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(time) ) as age
                      FROM message
                      WHERE folder='N' and parentid=$parent AND open="Y" AND folderid=$folderid
                      };
  my $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
  my $rv = int ( $sth->execute() );
  my $childcount = $rv;
  if ($rv > 0) 
  {
    my ($messageid, $age);
    while ( my @ary = $sth->fetchrow_array () ) 
    {
      ($messageid, $age) = @ary;
      $todays_children ++ if $age < 1*24*3600;
      $weeks_children ++ if ($age < 7*24*3600);
      $sessions_children ++ if $age < $folder_session_age;
      ($total, $todays, $weeks, $sessions) = countthread($messageid);
      $childcount += $total || 0;
      $todays_children += $todays || 0;
      $sessions_children += $sessions || 0;
      $weeks_children += $weeks || 0;
    }
  }
  return ($childcount, $todays_children, $weeks_children, $sessions_children);
}

sub countfolders 
{
  my($parent) = @_;
  my $foldercount;
####### original
#  my $sql_query = qq{
#        SELECT message.id
#        FROM message, user, members
#        WHERE folder='Y' AND folderid=$parent 
#            AND (
#                  ( message.name NOT LIKE ".%" AND message.name NOT LIKE "~.%" ) # not hidden
#                OR ( # hidden
#                    (message.name LIKE ".%" OR message.name LIKE "~.%")
#                    AND ( message.userid=user.id # user is the owner of the folder
#                        OR ( ( message.groupid=members.groupid AND members.userid=user.id ) # user is in group
#                              AND message.groupr='Y' ) # and folder is group readable
#                        )
#                    )
#                )
#        GROUP BY message.id
#        };
  my $sql_query = qq{
        SELECT message.id
        FROM message, members
        WHERE folder='Y' AND folderid=$parent 
            AND (
                  ( message.name NOT LIKE ".%" AND message.name NOT LIKE "~.%" ) # not hidden
                OR ( # hidden
                    (message.name LIKE ".%" OR message.name LIKE "~.%")
                    AND ( message.userid=$our_userid # user is the owner of the folder
                        OR ( ( message.groupid=members.groupid AND members.userid=$our_userid) # user is in group
                              AND message.groupr='Y' ) # and folder is group readable
                        )
                    )
                )
        GROUP BY message.id
        };
  if ( $name eq 'root' )    # override folder count for $name eq 'root'
  {
    $sql_query = qq{   
          SELECT message.id
          FROM message
          WHERE folder='Y' AND folderid=$parent
          GROUP BY message.id
          };
  }
  my $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
  my $rv = int ( $sth->execute() );
  $sth->finish ();
  return($rv)
}

sub print_folder 
{
  my $query = qq{SELECT name from user where id=$ownerid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($folder_ownername) = $sth->fetchrow_array ();
  $sth->finish ();
  $query = qq{SELECT name from groups where id=$groupid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($folder_groupname) = $sth->fetchrow_array ();
  $sth->finish ();
  print qq{<a href="$cgi_url?folder=$folder&expand=$expand">};
  if ($open eq 'Y')
  {
    if ($folderid == 1)
    {
    print qq{<img src="/icons/crystal/32x32/filesystems/folder_home.png" width=32 height=32 alt="" border=0};
    print qq{ align="top">};
    }
    else
    {
    print qq{<img src="/icons/crystal/32x32/filesystems/folder_open.png" width=32 height=32 alt="" border=0};
    print qq{ align="top">};
    }
  }
  print qq{<img src="/icons/crystal/32x32/filesystems/folder2_locked.png" width=32 height=32 alt="" border=0\
           align="top">}
        if $open eq 'N';
  print qq{</a>};
  # print qq{ <b>$folder</b>};
  print qq{ <b>$breadcrumb_link</b>};
  print " [CLOSED]" if $open eq 'N';
  $linkfolder = $folder;
  $linkfolder =~ s/\ /%20/g;
# print attributes icon
  print qq{ <a href="$cgi_url?folder=$linkfolder&op=attr&expand=$expand" }.
        qq{title="modify this folder's attributes">}.
        qq{<img src="/icons/crystal/16x16/actions/configure.png" width=16 }.
        qq{height=16 border=0 align="middle"></a>} 
            if ( $our_userid == $ownerid || $name eq 'root' );
# print owner and group info
  print qq{<font class="navigation">};
  print qq{&nbsp;[};
  #print qq{<a name="folder owner" title="$folder_ownername is the owner of this folder">$folder_ownername</a>};
  # print qq{ | };
  print qq{<a name="folder owner" title="this folder belongs to the '$folder_groupname' group">};
  print qq{$folder_groupname</a>]};
  print qq{</font>};
  print br;
}

sub find_thread_root 
{
  my($id) = @_;
  my $rootid;
  my $query = qq{SELECT parentid, folderid FROM message WHERE id=$id};
  my $sth = $dbh -> prepare ($query) || die $dbh->errstr;
  $sth->execute();
  my ($parentid, $folderid) = $sth->fetchrow_array ();
  $sth->finish ();
  if ($parentid == $folderid) 
  {
    $rootid = $id;
  }
  else 
  {
    $rootid = find_thread_root($parentid);
  }
  return ($rootid);
}

sub regress_folders 
{
  my($parent_folderid) = @_;
  my ($path) = '';
  if ($parent_folderid > 0) 
  {
    until ($parent_folderid == 1) 
    {
      my $query = qq{SELECT name, parentid
                     FROM message
                     WHERE id=$parent_folderid};
      my $sth = $dbh -> prepare ($query) || die $dbh->errstr;
      $sth->execute();
      my $folder_name;
      ($folder_name, $parent_folderid) = $sth->fetchrow_array ();
      $path = $folder_name."/".$path;
    }
    $sth->finish ();
  }
  return ($path);
}

sub print_title 
{
  print $title;
}

sub passkey 
{
  my @chars = ( "A" .. "Z", "a" .. "z", 0 .. 9 );
  my $passkey = join("", @chars[ map { rand @chars } (1 .. 20) ]);

# create a passkey with the username as an MD5 seed
  $passkey = md5_hex($name.$passkey);

  if ($name) # only do this update if a user is logged (don't want the warning for new users
  {
    # issue UPDATE to store new passkey
    my $query = qq{UPDATE $user_table SET passkey = "$passkey", counter=0 WHERE name="$name"};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    $sth->finish ();
  }

  return("$passkey");
}

sub session_age 
{
  my ($session) = @_;
  # get time in secs, then convert back to days
  if ($session) 
  {
    my $query = qq{SELECT ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP('$session') )};

    my $sth = $dbh->prepare ($query);
    $sth->execute ();
    my ($session_age) = $sth->fetchrow_array ();
    $sth->finish ();
    return($session_age);
  }
  else 
  {
    return(0);
  }
}

sub folder_session_age 
{
  my ($session_folderid) = @_;
  my ($folder_dtime, $folder_timestamp, $folder_session, $folder_session_age);
  if ($our_userid > 0 ) # we don't want to mess around if user not logged in.
  {
    my $query = qq{SELECT
                    ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(timestamp) ), 
                    timestamp, session
                    FROM sessions
                    WHERE user=$our_userid AND folder=$session_folderid
                    };
    my $sth = $dbh->prepare ($query);
    my $rv = int ( $sth->execute () );
    if ($rv > 0 ) 
    {
      ($folder_dtime, $folder_timestamp, $folder_session) = $sth->fetchrow_array ();
      $sth->finish ();
    }
    else  # if session info does not exist for this user in this folder, we'll just create
    {      # it from the general user session data that we already have in memory
      $sth->finish ();
      $query = qq{INSERT INTO sessions
                  SET folder=$session_folderid,
                      user=$our_userid,
                      timestamp=$session,
                      session=$session};
      $sth = $dbh->prepare ($query);
      $sth->execute ();
      $folder_dtime = $delta_time; # just use general delta time
      $folder_timestamp = $timestamp;
      $folder_session = $session;
      $sth->finish ();
    }
    if ( ( $op eq 'add entry'         # do not update session on new entries
          || $op eq 'preview'         # ... or previews of new entries
          || $op eq 'new entry'       # ... or the start of creating new entries
          || $op eq 'expand'          # ... or when a folder expansion is changed
          || $op eq 'isolate'          # ... or when a thread is isolated for printing
          || $op eq 'edit'             # ... or when a user edits one of his own messages
          || $op eq 'save changes'     # ... or when a user saves changes after editing a message
          || defined(param('page')) )  # ... or the user requests a specific page in folder
        && $folderid == $session_folderid )
    { # if we're in the folder, and we've added a new entry, we'll preimptively update the
           # update the folder timestamp but hold the session
      update_folder_session($session_folderid);
    }
    elsif ( $folder_dtime > $session_timeout && $folderid == $session_folderid)
    {
      $folder_session = $folder_timestamp;
      $query = qq{UPDATE sessions
                  SET session=DATE_FORMAT('$folder_timestamp', '%Y%m%d%H%i%s'), timestamp=now(), counter=0
                  WHERE user=$our_userid AND folder=$session_folderid};
      $sth = $dbh->prepare ($query);
      $sth->execute ();
      $sth->finish ();
    }
    elsif ( $folderid == $session_folderid )
    { # if we're in the folder, but folderd_time not sessioned out
           # update folder timestamp
      update_folder_session($session_folderid);
    }
    else 
    {
      $folder_session = $folder_timestamp;
    }
    $folder_session_age = session_age($folder_session);
    $sth->finish ();
  }
  else 
  {
    $folder_session_age = 0;
  }
  return($folder_session_age); # folder_session_age in secs
}

sub user 
{
  # Check for passkey in cookie or NULL
  my ($passkey) = cookie(-name=>"$cookie_name") || 'NULL';
# trimming the '-$name' off the passkey only needs to stay in the code until all of the old
# cookies are gone, then it can be tossed....  Mon Aug 28 21:57:21 MDT 2006
  ($passkey, $name) = split('\-', $passkey, 2); # split the cookie, limited to 2 parts
  chomp $passkey;

  # If not $session_timeout defined, define it as 2 minutes
  $session_timeout = 120; # 120 secs = 2 min for session time outs.

  if ($passkey ne 'NULL') 
  {
    my $query = qq{SELECT id, name, date_format(now(), '%e.%b.%Y %h:%i %p'),
                ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(timestamp) ),
                timestamp, session, thread
                FROM $user_table
                WHERE passkey='$passkey'};
    my $sth = $dbh->prepare ($query);
    $sth->execute ();
    my @ary = $sth->fetchrow_array ();
    ($our_userid, $name, $current_system_time, $delta_time, $timestamp, $session, $user_thread_style) = @ary;
    $username = $name;
    $user_thread_style = 0 unless defined($user_thread_style); # make sure $user_thread_style is defined

    if ($username) 
    {
      $query = qq{SELECT groupid
                  FROM members
                  WHERE userid=$our_userid};
      $sth = $dbh->prepare ($query);
      $sth->execute ();
      while (@ary = $sth->fetchrow_array ()) 
      {
        my ($this_group) = @ary;
        # push (@users_groups, $this_group);
        $users_groups{$this_group} = 'Y';
      }
    $sth->finish ();
    }
    
  $sth->finish ();
  }

  # set delta_time to 0 if no user
  $delta_time = 0 unless $delta_time;
  ################################# this may cause trouble, so keep an eye out for bugs.

  # if the time since last page acess (delta_time) is > the defined session timeout
  # ($session_timeout), reset the session to the last timestamp
  if ($delta_time > $session_timeout) 
  {
    $session = $timestamp; # set $session to the last timestamp
    # Put the last timestamp into the session column
    $query = qq{UPDATE $user_table SET session='$timestamp' WHERE id=$our_userid};
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    $sth->finish ();
  }
  $session = 'NULL' unless $session; # set a default value if $session not defined

  $name = '' if !$name;     # just to get rid of the warnings....
  if ($name ne '') 
  {
    # see if user is a member of the group that owns the folder
    my $query = qq{SELECT userid
                FROM members
                WHERE userid=$our_userid 
                AND groupid=$groupid};
    my $sth = $dbh->prepare ($query);
    $sth->execute ();
    my ($answer) = $sth->fetchrow_array ();
    $sth->finish ();
    $groupmember = 'Y' if $answer;

    unless ( $op eq 'norefresh'  || $op eq 'clean output' || $op eq 'dlarchive' || $op eq 'publish' ) 
    {
      my $passkey = passkey();

      $cookie_domain = $domain_name;
      $cookie_domain =~ s/^\.//;     # clear off any leading "."
      $cookie_domain =~ s/^www\.//;  # clear off any leading "www."
      $cookie_domain = ".".$cookie_domain;  # stick a "." on the front of the domain name
      $cookie = cookie(-name=>"$cookie_name",  # this is the cookie name
                       -value=>"$passkey",     # this is the fresh passkey
                       -path=>'/',             # we want the cookie available from any path
                       -domain=>"$cookie_domain",  # our modified cookie_domain
                       -expires=>'+15d');      # expire cookie in 15 days.
    }
    else 
    {
      $query = qq{UPDATE user SET timestamp=now() WHERE id=$our_userid};
      $query = qq{UPDATE user SET timestamp=now(), counter=0 WHERE id=$our_userid} if $op eq 'norefresh';
      $sth = $dbh->prepare ($query);
      $sth->execute ();
      $sth->finish ();
    }

    # see who's been online "recently"
    # I'm going to attempt to restrict user visability to group members.....
    $query = qq{SELECT name 
                FROM user, members as ours, members as thiers
                WHERE date_sub(now(), interval $recent_interval minute) < timestamp
                      AND ours.userid = $our_userid
                      AND thiers.groupid = ours.groupid
                      AND user.id = thiers.userid
                      GROUP BY user.id
                };
    $query = qq{SELECT name
                FROM user 
                WHERE date_sub(now(), interval $recent_interval minute) < timestamp
                } if $our_userid == 1;
    $sth = $dbh->prepare ($query);
    $sth->execute ();
    while ( my @ary = $sth->fetchrow_array () ) 
    {
      my ($online_name) = @ary;
      $currently_online .= "$online_name, "; # if $online_name ne $name;
    }
    $sth->finish ();
    $currently_online =~ s/,\ $//;
  }
  elsif ($name eq '') { $our_userid = 0; $groupmember=0; $session_age=0;}

  if ($op eq 'login') 
  {
    $username = param('username') if param('username');
    my $password = param('password') if param('password');
    if ($username && $password) 
    {
      # issue SELECT to verify user and password
      $query = qq{SELECT name, password, timestamp FROM $user_table WHERE name='$username'};
      $sth = $dbh->prepare ($query);
      $sth->execute ();
      my @ary = $sth->fetchrow_array ();
      my $pass_md5;
      ($name, $pass_md5, $session) = @ary;

      if ($name =~ /^!/ ) # check for unconfirmed username
      {
        print header();
        print "This account has not been confirmed. Contact <b><i>$title_tag</i></b>".
              " Administrator.\n";
        $sth->finish ();
        $dbh->disconnect ();
        exit(0);
      }

      if ($pass_md5 eq md5_hex('deactivate') ) 
      {
        print header();
        print "This account has been deactivated. Contact <b><i>$title_tag</i></b>".
              " Administrator.\n";
        $sth->finish ();
        $dbh->disconnect ();
        exit(0);
      }

      if ($pass_md5 ne md5_hex($password) ) 
      {
        print header();
        print "Authorization Failure.\n",br,br;
        print qq{<a href="$cgi_url?op=login">login</a>};
        $sth->finish ();
        $dbh->disconnect ();
        exit(0);
      }

      # update session to our last timestamp (reguardless of delta_t)
      $query = qq{UPDATE $user_table SET session='$session' WHERE name='$username'};
      $sth = $dbh->prepare ($query);
      $sth->execute ();
      $sth->finish ();

      my $passkey = passkey();

      $cookie_domain = $domain_name;
      $cookie_domain =~ s/^\.//;     # clear off any leading "."
      $cookie_domain =~ s/^www\.//;  # clear off any leading "www."
      $cookie_domain = ".".$cookie_domain;  # stick a "." on the front of the domain name
      my $cookie = cookie(-name=>"$cookie_name",
                         -value=>"$passkey",
                          -path=>'/',
                         -domain=>"$cookie_domain",
                         -expires=>'+15d');
      print redirect(-cookie=>[$cookie],
                     -url=>"$cgi_url?folder=$folder");
    }
    else 
    {
      print header();
      print qq{<SCRIPT LANGUAGE="JavaScript">
      <!-- Original:  Tom Khoury (twaks\@yahoo.com) -->

      <!-- This script and many more are available free online at -->
      <!-- The JavaScript Source!! http://javascript.internet.com -->

      <!-- Begin
      function placeFocus() \{
        if (document.forms.length > 0) \{
          var field = document.forms[0];
          for (i = 0; i < field.length; i++) \{
            if ((field.elements[i].type == "text") || (field.elements[i].type == "textarea") || (field.elements[i].type.toString().charAt(0) == "s")) \{
            document.forms[0].elements[i].focus();
            break;
            \}
          \}
        \}
      \}
      //  End -->
      </script>};
      print start_html(-title=>"$title_tag - login",
                       -bgcolor=>"$page_background",
                       -OnLoad=>"placeFocus()",
                       -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                       -style=>{-src=>'/re_default.css'});
      print qq{<LINK REL="SHORTCUT ICON" href="/favicon.ico">\n}; # tell browser where to get the favicon.ico
      print qq{<table width=400 border=0 cellpadding=6 cellspacing=3><tr><td bgcolor=#EEEEEE>};
      print start_form(-action=>"$cgi_url", -method=>"post");
      print qq{<b>Login</b>},br;
      print qq{<table border=0>};
      print qq{<tr><td align=right>username: </td><td>},textfield(-name=>"username"),
            qq{</td></tr>};
      print qq{<tr><td align=right>password: </td><td>},password_field(-name=>"password"),
            qq{</td></tr>};
      print qq{</table>};
      print qq{<input type="hidden" name="folder" value="$folder">};
      print submit(-name=>"op", -value=>"login"),br;
      print qq{</td></tr><tr><td bgcolor=#EEEEEE>};
      print qq{If you do not yet have an account, you can...},br;
      print submit(-name=>"op", -value=>"create new account"),br;
      print qq{</td></tr><tr><td bgcolor=#EEEEEE style="border: solid red; border-width: 1px; ">};
      print qq{<font style="font-weight: bold; ">Forgot your password?</font>},br;
      print qq{If you have an account, but have forgotten your password, }.
            qq{enter your e-mail address below and click on the \"mail password\" button.},br;
      print qq{<font style="color: red; font-size: 80%; font-weight: bold; "><i>This will create a new, random password for your account, and send it to your email address.</i></font>},br;
      print qq{e-mail address: },textfield(-name=>"email"),br;
      print submit(-name=>"op", -value=>"mail password"),br;
      print qq{<font style="color: red; font-size: 80%; font-weight: bold; "><i>After receiving your new password, you need to log in, and change your password back to something you will be able to remember.</i></font>},br;
      end_form();
      print qq{</td></tr><tr><td bgcolor=#EEEEEE>};
      print qq{<a href=$cgi_url?>Cancel and return to $title_tag</a>},br;
      print qq{</td></tr></table>};
      end_html();
      exit(0);
    }
  }
  elsif ($op eq 'logout' && $name) 
  {
    $cookie_domain = $domain_name;
    $cookie_domain =~ s/^\.//;     # clear off any leading "."
    $cookie_domain =~ s/^www\.//;  # clear off any leading "www."
    $cookie_domain = ".".$cookie_domain if $cookie_domain;  # stick a "." on the front of the domain name
    my $cookie = cookie(-name=>"$cookie_name",
                         -value=>"",
                         -path=>'/',
                         -domain=>"$cookie_domain",
                         -expires=>'-1m');
    print redirect(-cookie=>[$cookie],
                   -url=>"$cgi_url?folder=$folder");
    print start_html();
    passkey(); # set new passkey so that an eves dropper can't reenter
               # the site using the old passkey. This is just added security
               # for the extreemly paranoid
  }
  $sth->finish ();
  return($name);
  $session_age = session_age($session);
}

sub quick_jump_menu 
{
  my ($parent_id,$parentage) = @_;
  my $new_messages;
  $leader = "&nbsp;&nbsp;&nbsp;";
  $leaders += 1;

  # if we're at the first level, we'll start the form and set things up.
  if ($parent_id == 1) 
  {
    print qq{<FORM NAME="menu" METHOD="POST" }.
          qq{ACTION="$cgi_url"> }.
          qq{<SELECT NAME="folder">};

    print qq{<OPTION VALUE="$root_folder_name"};
    print qq{>$root_folder_name};

    # check for new messages (session) and print if they exists
    $folder_session_age = folder_session_age(1);
    ($new_messages) = countsessionchildren(1);
    print " ($new_messages)" if ($new_messages > 0);

    # Print the "you are here arrow" in the SELECT menu
    print qq{ &lt;---- you are here } if (1 == $folderid);
  }
  # get all of the folders that we have access to from here....
  my $query = qq{SELECT name, id
                FROM message, members
                WHERE folder='Y' AND parentid=$parent_id AND open='Y'
                    AND ( (groupr='Y' AND members.groupid=message.groupid AND members.userid=$our_userid)
                        OR ( otherr='Y'
                          AND ( name NOT LIKE '.%'          # invisible folders
                                AND name NOT LIKE '~.%' ))  # more invisible folders
                      OR message.userid=$our_userid # users see their own folders
                      OR '$name'='root') # root sees all folders
                GROUP BY name
                ORDER BY name};
  my $sth = $dbh->prepare ($query);
  $sth->execute ();
  $folder = '' if $folder eq $root_folder_name;
  while ( my @ary = $sth->fetchrow_array () ) 
  {
    my ($childfolder, $childfolderid) = @ary;
    my $path = "$folder/$childfolder";
    $path =~ s/^\///;
    my $linkpath = $path;
    $linkpath =~ s/\ /%20/g;
    $parentage = "" unless $parentage;
    print qq{<OPTION VALUE="$parentage/$childfolder"};
    print qq{ SELECTED } if ($childfolderid == $folderid);
    print qq{>};
    print $leader x $leaders."|--";
    print qq{&gt;} if ($childfolderid == $folderid);
    print " $childfolder";

    # check for new messages (session) and print if they exists
    $folder_session_age = folder_session_age($childfolderid);
    ($new_messages) = countsessionchildren($childfolderid);
    print " ($new_messages)" if ($new_messages > 0);

    # Print the "you are here arrow" in the SELECT menu
    print qq{ &lt;---- you are here } if ($childfolderid == $folderid);

    quick_jump_menu($childfolderid,"$parentage/$childfolder");
  }
  $sth->finish ();
  $leaders -= 1;
  if ($parent_id == 1) 
  {
    print qq{</SELECT>};
    print qq{<input type="hidden" name="expand" value="$expand">};
    print qq{<INPUT TYPE="submit" VALUE="Jump">};
    print qq{</FORM>};
  }
}

sub quick_folder_count
{
  my ($parent_id) = @_;
  my ($folder_session_count) = 0;

  my $query = qq{SELECT folder.id
                 FROM message folder, members, sessions, user
                 WHERE folder.folder='Y' AND folder.open='Y'
                       AND sessions.folder=folder.id AND user.id=$our_userid
                       AND folder.folderid=$parent_id
                       AND members.userid=user.id
                       AND sessions.user=user.id
                       AND ( (folder.groupr='Y' AND members.groupid=folder.groupid)
                            OR ( folder.otherr='Y'
#                              AND ( folder.name NOT LIKE '.%' 
#                                    AND folder.name NOT LIKE '~.%' ))
            AND (
                  ( folder.name NOT LIKE ".%" AND folder.name NOT LIKE "~.%" ) # not hidden
                OR ( # hidden
                    (folder.name LIKE ".%" OR folder.name LIKE "~.%")
                    AND ( folder.userid=user.id # user is the owner of the folder
                        OR ( ( folder.groupid=members.groupid AND members.userid=user.id ) # user is in group
                              AND folder.groupr='Y' ) # and folder is group readable
                        )
                    )
                )
                )
                        OR folder.userid=$our_userid
                        OR '$name'='root')
                  GROUP BY folder.id};
  my $sth = $dbh->prepare ($query);
  $sth->execute ();
  while ( my @ary = $sth->fetchrow_array () ) 
  {
    my ($sub_folderid) = @ary;
    $folder_session_count += quick_folder_count($sub_folderid);
  }

# ORIGINAL QUERY -- UNMODIFIED FOR ROLL-BACK PURPOSES
#  $query = qq{SELECT message.id
#                 FROM message message, message folder, members, sessions, user
#                 WHERE folder.folder='Y' AND message.folder='N' AND folder.open='Y'
#                       AND message.open='Y' AND sessions.folder=folder.id AND user.id=$our_userid
#                       AND message.folderid=folder.id AND folder.folderid=$parent_id
#                       AND message.time > sessions.timestamp AND members.userid=user.id
#                       AND sessions.user=user.id
#                       AND ( (folder.groupr='Y' AND members.groupid=folder.groupid)
#                            OR ( folder.otherr='Y'
#                              AND ( folder.name NOT LIKE '.%' 
#                                    AND folder.name NOT LIKE '~.%' ))
#                        OR folder.userid=$our_userid
#                        OR '$name'='root')
#                  GROUP BY message.id};
# first optimization pass
#  $query = qq{SELECT message.id
#                 FROM message, sessions
#                 WHERE 
#                       message.folderid=$parent_id
#                       AND message.open='Y' AND sessions.folder=$parent_id
#                       AND sessions.user=$our_userid
#                       AND message.time > sessions.timestamp
#                  GROUP BY message.id};
# second optimization pass... the first pass picked up too much, this one 
# actually seems to be a little faster, too.
  $query = qq{SELECT message.id
                 FROM message message, message folder, sessions
                 WHERE folder.folder='Y' AND message.folder='N' AND folder.open='Y'
                       AND message.open='Y' AND sessions.folder=folder.id
                       AND message.folderid=folder.id AND folder.folderid=$parent_id
                       AND message.time > sessions.timestamp
                       AND sessions.user=$our_userid
                  GROUP BY message.id};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  $folder_session_count += int ( $sth->execute() );
  $folder = '' if $folder eq $root_folder_name;

  $sth->finish ();
  return($folder_session_count);
}

sub move_folder 
{
  my ($parent_id,$parentage) = @_;
  $leader = "&nbsp;&nbsp;&nbsp;";
  $leaders += 1;

  return() if $folderid == 1; # don't show  a "Move to:" menu for the root folder
  # if we're at the first level, we'll start the form and set things up.
  if ($parent_id == 1) 
  {
    print "Move to: <select name=new_parent>\n";

    print qq{<OPTION VALUE="1"};
    print qq{ SELECTED } if ($parentid == 1);
    print qq{>$root_folder_name};

    # Print the "you are here arrow" in the SELECT menu
    print qq{ &lt;---- "$foldername" is here } if ($parentid == 1);
  }
  # get all of the folders that we have access to from here....
  my $query = qq{SELECT message.name, message.id, message.parentid, groups.name
              FROM message, members, groups
              WHERE folder='Y' AND parentid=$parent_id AND open='Y'
                  AND ( (groupr='Y' 
                        AND members.groupid=message.groupid
                        AND members.userid=$our_userid)
                    OR ( otherr='Y' 
                        AND ( message.name NOT LIKE '.%' 
                        AND message.name NOT LIKE '~.%' 
                            )
                        )
                    OR message.userid=$our_userid
                    OR '$name'='root') # root sees all folders
                  AND message.groupid = groups.id
              GROUP BY message.name
              ORDER BY message.name};
  my $sth = $dbh->prepare ($query);
  $sth->execute ();
  $folder = '' if $folder eq $root_folder_name;
  while ( my @ary = $sth->fetchrow_array () ) 
  {
    my ($childfolder, $childfolderid, $folder_parentid, $group_name) = @ary;
    next if $childfolderid == $folderid; # can't move a folder to it's own children
    my $path = "$folder/$childfolder";
    $path =~ s/^\///;
    my $linkpath = $path;
    $linkpath =~ s/\ /%20/g;
    $parentage = "" unless $parentage;
    print qq{<OPTION VALUE="$childfolderid"};
    print qq{ SELECTED } if ($childfolderid == $parentid);
    print qq{>};
    print $leader x $leaders."|--";
    print qq{&gt;} if ($childfolderid == $parentid);
    print " $childfolder (group: $group_name)";

    # Print the "you are here arrow" in the SELECT menu
    print qq{ &lt;---- "$foldername" is here } if ($childfolderid == $parentid);

    move_folder($childfolderid,"$parentage/$childfolder");
  }
  $sth->finish ();
  $leaders -= 1;
  if ($parent_id == 1) 
  {
    print qq{</SELECT>};
  }
}

sub update_folder_session
{
  my ($session_folderid) = @_;
  my $query = qq{UPDATE sessions
                SET timestamp=now()
                WHERE user=$our_userid 
                AND folder=$session_folderid};
  my $sth = $dbh->prepare ($query);
  $sth->execute ();
  $sth->finish ();
}

sub print_contents
{
  my ($contents) = @_;
  $contents =~ s#http://">##g;
  my @contents = split (/\r?\n/, $contents);
  my $tag_count;

  foreach $contents (@contents)
  {
# convert apparent URLs to linked URLs
    $contents =~ s#(http:\/\/[^() \[\]\t",;:<>@\\\^'{}\|\n]+)#<a href="$1">$1<\/a>#ig
          unless ( $contents =~ m/<a href/i || $contents =~ m/<img src/i );
    $contents =~ s#(https:\/\/[^() \[\]\t",;:<>@\\\^'{}\|\n]+)#<a href="$1">$1<\/a>#ig
          unless $contents =~ m/<a href/i;
# trim excessively long URLs for length
    $contents =~ s#>(http\S{60})\S{4,}(\S{7})</a>#>$1....$2</a>#ig; # trim long URLs to make them pretty
# convert apparent email addressed to linked email addresses
    $contents =~ s#(\w\S*\@\w[\w\-\.]*\w)#<a href="mailto:$1">$1</a>#g
          unless $contents =~ m/href="*mailto/i;
# convert potential character problems to HTML escape sequences
    $contents =~ s#\x91#\&lsquo;#g; #left, single quote
    $contents =~ s#\x92#\&rsquo;#g; #right, single quote
    $contents =~ s#\x93#\&ldquo;#g;
    $contents =~ s#\x94#\&rdquo;#g;
    $contents =~ s#\x96#\&ndash;#g;
    $contents =~ s#\x97#\&mdash;#g;

# test for start of table, list, or blockquote
# if start of table increment "in_table" flag to supress space translation
    if ($contents =~ /<table/i)
    { 
      $_ = $contents;
      my @tag_count = m#<table#gi;
      $tag_count = @tag_count;
      $in_table += $tag_count;
    }
# if start of list or blockquote, increment "in_block" flag to supress space translation
    if ($contents =~ /<(ol|ul|blockquote)/i)
    {
      $in_block++;
    }
    
# space translation is turned off while rendering tables
    unless ( $in_table > 0 || $in_block > 0 )
    {
      # convert double spaces to ' &nbsp';
      $contents =~ s#  #\&nbsp; #g;
      # odd-man catcher
      $contents =~ s#  #\&nbsp; #g;
      # convert leading spaces to &nbsp;
      $contents =~ s#^ #\&nbsp;#;
    }

# test for end of table, list, or blockquote
# if end of table, decrement "in_table" flag to reactivate space translation
    if ($contents =~ /<\/table/i)
    { 
      $_ = $contents;
      my @tag_count = m#</table#gi;
      $tag_count = @tag_count;
      $in_table -= $tag_count;
    }
# if $in_table < 0, we have a problem... kill the </table> tags, incriment the counter
    while ( $in_table < 0 )
    {
      $contents =~ s#</table.*?>##i;
      $in_table++;
    }
# test for end of list, or blockquote
# if end of list or blockquote, decrement "in_block" flag to reactivate space translation
    if ($contents =~ /<\/(ol|ul|blockquote)/i)
      { $in_block--; }

    # HTML tag filter based on @approved_tags list at top
    $_ = $contents;
    my @tags = m#<.*?>#g;
    my ($test_tag, $approved_tag);
    EACH_TAG: foreach my $tag (@tags)
    {
      # warn ("tag: $tag\n");
      $test_tag = $tag;
      $test_tag =~ tr/A-Z/a-z/;
      $test_tag =~ s#</*\s*(\S*).*\s*/*>#$1#;
      TAG_TEST: foreach $approved_tag (@approved_tags)
      {
        next EACH_TAG if $approved_tag eq $test_tag; # tag good, next
      }
      $contents =~ s#$tag##g; # remove the tag if not in approved list
    }

    # highlight search terms in message name to make them easy finding
    foreach $mark(@marks)
    {
      $contents =~
           s/\b($mark)\b/<font style="background-color: $fresh_title_background; ">$1<\/font>/gi;
    }
    print $contents;
    print br unless ( $contents =~ m/<\/?(ol|ul|li|blockquote|table|th|tr|td|br|\/p|dt|dd)>/i );
    print "\n";
  }
# if $in_table > 0, we have a problem... inser enough </table> tags, 
# to close all tables and incriment the counter
  while ( $in_table > 0 )
  {
    print qq{</table>\n};
    $in_table--;
  }
  print qq{</td>\n</tr>\n<tr>\n<td bgcolor='$message_background'>\n} if $in_block > 0;
  $in_table=0; #reset $in_table to 0 just in case.
  $in_block=0; #reset $in_block to 0 just in case.
}

sub send_confirm_message # subroutine to send a confirmation link to user
{
  my ($user_name) = @_;
  $query = qq{ SELECT user.passkey, user.email
                FROM user
                WHERE user.name="!$user_name"
                };
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  @ary = $sth->fetchrow_array ();
  my ($passkey, $email) = @ary;

  my $temp_domain_name = $domain_name;
  $temp_domain_name =~ s/^\.//; # strip leading '.' from beginning of domain name
                                # if present.

# $temp_user_name will hold the HTML escaped username for confirmation
  my $temp_user_name = escapeHTML($user_name);
  $temp_user_name =~ s/\ /\%20/g;
#  warn "$temp_user_name\n";

  my $message = <<END_OF_MESSAGE;
Your $title_tag login is $user_name
http://$temp_domain_name$cgi_url

The password is case sensitive.

To confirm your account, click on the following link, or paste it into your browser's location field.
http://$temp_domain_name$cgi_url?op=confirm&user=$temp_user_name&secret=$passkey

You may not have full access to the message base until
the admin has added your account to the appropriate groups.

Do not reply to this message.
END_OF_MESSAGE

  my $mailer = Mail::Mailer->new();
  $mailer->open({'From' => "$notification_address",
                'To' => "$email",
                'Subject' => "Your $title_tag login"} );
  print $mailer "$message";
  close($mailer);
}

sub human_readable
{
  my ($size) = @_;

  my $units = "b&nbsp;";
  if ($size > 1000)
    {
      $size = $size/1024;
      $units = "kb";
    }
  if ($size > 1000)
    {
      $size = $size/1024;
      $units = "mb";
    }
  if ($size > 1000)
    {
      $size = $size/1024;
      $units = "gb";
    }
  if ($size > 1000)
    {
      $size = $size/1024;
      $units = "tb";
    }
  $size = sprintf("%.1f $units", $size) unless ( $units eq "b");
  $size = "$size $units&nbsp;" if ( $units eq "b");
  $size =~ s/ /&nbsp;/g;

  return($size);
}

# various tools for testing

sub explain_query # subroutine to help with query optimization
{
  my ($test_query) = @_;
  $test_query =~ s/\n//g;
  $test_query =~ s/\t/ /g;
  warn (qq{EXPLAIN $test_query;\n});
}
# for testing queries
#  $query =~ s/\n//g;
#  $query =~ s/\t/ /g;
#  $query =~ s/\ \ */ /g;
#  $query =~ s/\ \ */ /g;
#  warn ("QUERY: $query\n");