#! /usr/bin/perl -T -I/var/www/perl-bin

# RealizationEngine
my $version = "2.5.0c";

# Copyright Realization Systems, Inc., 2002, 2003, 2004, 2005
# 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;

# 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 warnings;
use CGI qw(:cgi :form :html);
use DBI;
use POSIX;
use Digest::MD5 qw(md5_hex);
use Mail::Mailer;
use Fcntl qw/O_WRONLY O_CREAT O_EXCL/;
# use CGI::Carp qw(fatalsToBrowser warningsToBrowser carpout);
# use Benchmark;
# use vars qw/$t0 $t1 $td $tf/;
use strict;

# detaint ENV{'PATH'}
$ENV{'PATH'} = '/bin:/usr/bin';
delete @ENV{'IFS', 'CDPATH', 'ENV', 'BASH_ENV'};

use vars qw/$icon $text/; # theme and localization hashrefs
# database vars
use vars qw/$sth $dbh /;
# user data
use vars qw/$name %users_groups $folder_session_age $session_age $session /;
# system stuff
use vars qw/$op $cookie $expand $userid $groupid $our_userid $our_userid $open 
            $foldergroupid $group $folderid $ownerid $groupmember $groupw
            $parentfolderid $foldername $groupname $contents @group @other $error
            %allowed_table $in_table $in_block $expandmessage @folder $parentid
            $groupr $otherr $otherw $folderURI $username $timestamp $session_timeout 
            $delta_time $cookie_domain $linkfolder $page $pages $limit @folder_array
            %value $current_system_time $url_folder $message_userid $message_userid
            $stylesheet
            /;

use vars qw/$total $todays $weeks $sessions/; # declarations for sub countthread 
use vars qw/$leader $leaders/; # declarations for sub quick_jump_menu

# $hooks is the API hooks hashref for plugins
use vars qw/$hooks @plugins $plugin_op_array $plugin_op_hash/;
$hooks = { 'message_body' => [], # plugins that affect message bodies
           'message_compose_bottom' => [], # stuff that appears at the bottom of the message composition pages
           'message_data' => [], # applies to none-contents data of a message
           'folder_navigation_area' => [], # inside the folder navigation table, to the right of the folder nav links
        };
$plugin_op_array = [];
$plugin_op_hash = {};
my $plugin_registry = "plugins/registry"; # location of plugin registry
eval { require "plugins/registry"; };
foreach my $plugin (@plugins) { # parse the plugin registry
  eval { require $plugin; } || warn ("plugin loading failed, $plugin: $!");
}

$name = qq{};

my $home_dir = my $doc_root = $ENV{"DOCUMENT_ROOT"};

if ($home_dir =~ m#^(/home/[\w/.-]+/html)#) {
  $home_dir = $1;
}
else {
  warn ("doc_root failed test. --> $home_dir \n");
}
$home_dir =~ s/\/html//;
require "$home_dir/data/settings.re";
my ($dsn, $db_user, $dbpassword, $cgi_url) = &setup();
$cgi_url = script_name(); # path and name of RealizationEngine script

##########################
# icon theming
#
# later, we'll do things to allow the administrator to pick a theme from available themes
#
#########################
# eval { require "icon_themes/etiquette"; };
eval { require "$home_dir/icon_themes/icon-crystal"; };

##########################
# localization
#
# later, we'll do things to allow the administrator to pick a locale from available resources
#
#########################
eval { require "$home_dir/locale/default"; };

#########################
# set stylesheet
#########################
$stylesheet = "/re_default.css";
# $stylesheet = "/stylesheets/blah_blue.css";
# $stylesheet = "/stylesheets/purple_haze.css";

# connect to database
my $dbh = DBI->connect ($dsn, $db_user, $dbpassword);
# once connceted, get rid of the login information
undef $dsn; undef $db_user; undef $dbpassword;

unless (defined ($dbh) ) 
{
  print header();
  print $text->{failure_to_connect};
  exit(0);
}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

# $settings->{'title'} =~ s/\$(\w+)/$allowed_table{$1}/g;

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

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

$query = qq{SELECT value FROM settings WHERE name="P_privacy_statement"};
$sth = $dbh->prepare ($query);
$sth->execute ();
($settings->{'privacy_statement'}) = $sth->fetchrow_array () || $text->{privacy_statement};

$settings->{'page_footer'} =~ s/\$(\w+)/${$1}/g;

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

$sth->finish ();

# list of html tags deamed safe for enclussion in messages
my @approved_tags = qw /b i font a blockquote li ol ul table td tr th span embed object param 
              u sup sub p em strong strike tt div img q pre center dd dt/;

# list of folder name safe chars
my $legal_folder_chars = qr/^['\w \.\-_\?:~()]+$/;
my $legal_folder_chars_enum = "[word characters] as well as 0-9,.-_?:~'()";

$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;

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

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

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

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

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

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

my $expand = param('expand') || 1;
my $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 = qq{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.
my $currently_online = '';
$name = user();

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

####################################################
# begin operations
#
# this is where things actually get started....
####################################################

# Process any plugin defined operations


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=>"$settings->{'title_tag'} - $folder",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print start_form(-action=>$cgi_url, -method=>"post");
  print_title();
  print_folder();
  print qq{<h1 style="padding-left: 10%; ">$text->{create_folder_head}</h1>},br;
  print qq{<b>$text->{folder_name_label}:</b> },textfield(-name=>"foldername"),
        qq{ <font class="navigation" style="color: blue; "><i>$text->{folder_name_note}</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 qq{<b>$text->{group_label}:</b> <select name="groupname">};
  while ( @ary = $sth->fetchrow_array () ) {
    ($group, $foldergroupid) = @ary;
    print qq{<option value="$foldergroupid" };
    print qq{selected="selected"} if $foldergroupid == $groupid;
    print qq{ />$group};
    }
  $sth->finish ();
  print qq{</select>},br;
  print qq{</td><td>};
  print qq{ <font class="navigation" style="color: blue; ">$text->{select_group_note}};
  print qq{</font></td></tr></table>};
  print qq{<table border=0>};
  print qq{<tr><td><b>$text->{group_access_label}:</b> </td><td>},checkbox_group(-name=>'group',
                                        -values=>['Read','Write'],
                                        -default=>['Read','Write'],
                                        -labels=>$text->{access_labels},
                                        ),"</td>",
        qq{<td> <font class="navigation" style="color: blue; ">},
        qq{$text->{access_note_group_read}},br,
        qq{$text->{access_note_group_write}</font></td></tr>};
  print qq{<tr><td><b>$text->{other_access_label}:</b> </td><td>},checkbox_group(-name=>'other',
                                        -values=>['Read','Write'],
                                        -default=>[],
                                        -labels=>$text->{access_labels},
                                        ),"</td>",
        qq{<td> <font class="navigation" style="color: blue; ">},
        qq{$text->{access_note_other_read}},br,
        qq{$text->{access_note_other_write}</font></td></tr>};
  print qq{</table>};
  print qq{<b>$text->{folder_description_label}:</b> },
        br,textarea(-name=>"contents", -rows=>9, -columns=>80, wrap=>'virtual'),
        br,qq{ <em>$text->{folder_description_note}</em>},
        qq{<td> <font class="navigation" style="color: blue; ">},
        qq{$text->{folder_description_note2}</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 qq{<input type="hidden" name=folder value="$folder" />};
  print submit(-name=>'op', -value=>"add folder", -label=>$text->{add_folder_button_label});
  print qq{, or },submit(-name=>'op', -value=>"cancel", -label=>$text->{button_label_cancel});
  print qq{</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]),qq{folder = $folder},br,qq{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 =~ $legal_folder_chars ) {
    print header(-cookie=>[$cookie]),qq{$text->{illegal_folder_name_char}},br;
    $sth->finish ();
    $dbh->disconnect ();
    exit(0);
    }
  my $db_safe_foldername = $dbh->quote($foldername);
  $groupname = param('groupname')
    || print redirect(-cookie=>[$cookie], -location=>"$cgi_url?folder=$folder&NO_GROUP_NAME");
  $contents = param('contents');
  $contents = $dbh->quote($contents);
#  $contents =~ s#</?[^allow_HTML_tags][^>]*?>##gmi;
  @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 qq{@group},br,qq{@other};
  $error = "SOMETHING";
  if ( $foldername && $groupname ) {
    $query = qq{INSERT into message
                SET
                name=$db_safe_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 = escapeHTML ($parentfolder."/".$foldername);
  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=>"$settings->{'title_tag'} - $folder",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  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 qq{<input type="hidden" name="folder" value="$folder" />\n};
  print qq{<input type="hidden" name="expand" value="$expand" />};
  print qq{$text->{folder_rename_label}: },textfield(-name=>"new_message_name", -value=>"$foldername"),br;

  move_folder(1);

  print qq{<table cellpadding="0" cellspacing="0"><tr><td rowspan="2" valign="top">}.
        qq{$text->{permissions_dialog_note}&nbsp;</td>};
  print qq{<td align="right">$text->{group_label}:</td>};
  print qq{<td><input type="checkbox" name="new_group" value="Read" };
  print qq{CHECKED} if $groupr eq 'Y';
  print qq{ />$text->{access_note_read} };
  print qq{<input type="checkbox" name="new_group" value="Write" };
  print qq{CHECKED} if $groupw eq 'Y';
  print qq{ />$text->{access_note_write}};
  print qq{</td></tr>};
  print qq{<tr><td align="right">$text->{other_label}:</td>};
  print qq{<td><input type="checkbox" name="new_other" value="Read" };
  print qq{CHECKED} if $otherr eq 'Y';
  print qq{ />$text->{access_note_read} };
  print qq{<input type="checkbox" name="new_other" value="Write" };
  print qq{CHECKED} if $otherw eq 'Y';
  print qq{ />$text->{access_note_write}};
  print qq{</td>};
  print qq{</tr></table>};

  # Change Group -- users can change to another group they are a part of,
  # 'root' can change the folder to any group
  print qq{$text->{change_group_label}: <select name=new_groupid>};
  print qq{<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 qq{<option value="$newgroupid" };
    print qq{selected="selected"} if $newgroupid eq $groupid;
    print qq{ /> $groupname};
  }
  $sth->finish ();
  print qq{</select>},br;

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

  print qq{$text->{folder_description_label}: },br,
        textarea(-name=>"new_contents",
                 -rows=>25, columns=>70,
                 -default=>"$contents",
                 -wrap=>"virtual"),br;
  print qq{$text->{folder_open_dialog}: };
  print radio_group(-name=>'open_state',
                    -values=>['Y', 'N'],
                    -default=>$open,
                    -linebreak=>0,
                    -labels=>$text->{folder_open_labels},
                    ),br;
  print submit(-name=>"op", -value=>"update attributes", -label=>$text->{attributes_update_label});
  $sth->finish ();
  print end_form(), end_html();
  }
elsif ($op eq 'update attributes' && defined($name)
      && ( $our_userid == $ownerid || $name eq 'root' ) ) 
{
  $expand = param('expand') || 1;
  my $new_message_name = param('new_message_name');
#  $new_message_name =~ s#</?[^allow_HTML_tags][^>]*?>##gmi;
  unless ( $new_message_name =~ $legal_folder_chars ) 
  {
    print header(-cookie=>[$cookie]),"$text->{illegal_folder_name_char}";
    $sth->finish ();
    $dbh->disconnect ();
    exit(0);
  }
  if ( $new_message_name eq '' ) 
  {
    print header(-cookie=>[$cookie]),qq{$text->{warning_blank_folder_name}};
    $sth->finish ();
    $dbh->disconnect ();
    exit(0);
  }
  $new_message_name = $dbh->quote($new_message_name);
  my $new_parent = param('new_parent');
  $new_parent = $dbh->quote($new_parent);
  my $new_owner = param('new_ownerid');
  $new_owner = $dbh->quote($new_owner);
  my $new_group = param('new_groupid');
  $new_group = $dbh->quote($new_group);
  my $new_contents = param('new_contents');
  $new_contents = $dbh->quote($new_contents);
  my @new_group = param('new_group');
# define $new_group[0] and [1] to eliminate warnings
  $new_group[0] = '' unless $new_group[0];
  $new_group[1] = '' unless $new_group[1];
  my @new_other = param('new_other');
# define $new_other[0] and [1] to eliminate warnings
  $new_other[0] = '' unless $new_other[0];
  $new_other[1] = '' unless $new_other[1];
  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');
  $new_open_state = $dbh->quote($new_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,
                  time=date_format(now(), '%Y%m%d%H%i%s')
                  WHERE id = $folderid};
  # print header(), $update,br;
  $new_message_name = param('new_message_name');
  $folder =~ s{$folder[$#folder]}{$new_message_name}; # mung path for redirect
  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=>"$settings->{'title_tag'} - $folder",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});

  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 $settings->{'user_table'}.name
                  FROM user
                  WHERE $settings->{'user_table'}.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 ($parent) 
  {
    print qq{<table border=1><tr><td>};

    print qq{<table border=0 cellpadding=3 cellspacing=1>};
    print qq{<tr><td class="fresh_title">},
          qq{<b>$old_messagename</b></td></tr>};
    print qq{<tr><td class="fresh_body">};
    print qq{<font class="navigation"><b>};
    print qq{$text->{dialog_by} $old_username};
    print qq{</b></font>};
    $folder =~ s/\ /%20/g;
    print br;

    print_contents(\$old_contents);
    # print_contents($old_contents);

    print qq{</td></tr></table>};
    print qq{</td></tr></table>};
  }
  else 
  {
    $old_messagename = "";
  }
  print qq{$text->{message_title_dialog}: },textfield(-name=>"messagename", -value=>"$old_messagename"),br;
  print qq{$text->{message_body_dialog}:},br,textarea(-name=>"contents", -value=> "", -rows=>"10",
        -columns=>"80", -wrap=>"virtual"),br;
  print qq{$text->{url_dialog} },textfield(-name=>"URI", -size=>50,-value=>'http://'),br;
  print qq{&nbsp; }x28,
        qq{<font class="navigation">($text->{url_example_note})</font>},br;
  # print qq{&nbsp;&nbsp;&nbsp;&nbsp;Link name for the URL? }, textfield(-name=>"linkname",size=>50),br;
  print qq{&nbsp;&nbsp;&nbsp;&nbsp;$text->{attachment_dialog}: },filefield(-name=>"upload_file",size=>50),br;
  $folder =~ s/\%20/\ /g;
  print qq{<i>($text->{file_upload_limit_dialog} $settings->{'upload_limit_MB'} MB.)</i>},br;
  print qq{<input type="hidden" name=folder value=\"$folder\" />};
  print qq{<input type="hidden" name=folderid value=\"$folderid\" />};
  print qq{<input type="hidden" name=parent value=$parent />};
  print qq{<input type="hidden" name="expand" value="$expand" />};
  print submit(-name=>'op', -value=>"preview", -label=>$text->{button_label_preview});
  print submit(-name=>'op', -value=>"add entry", -label=>$text->{button_label_add_entry});
  print qq{ $text->{warning_attachment_preview}};
  print qq{, or },submit(-name=>'op', -value=>"cancel", -label=>$text->{button_label_cancel}),br;
  print qq{ <i><b>$text->{warning_allow_upload_time}</b></i>},br;
  print end_form(),end_html();

# run plugins that go at the bottom of the page
  for (my $plugin=0; $plugin<@{$hooks->{'message_compose_bottom'}}; $plugin++) {
    &{$hooks->{'message_compose_bottom'}->[$plugin]};
  }
}
elsif ($op eq 'preview') 
{  # this routine is not secured
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$settings->{'title_tag'} - $folder",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print start_multipart_form(-action=>$cgi_url, -method=>"post");
  print_title();
  print_folder();
  my $messagename = param('messagename') || " ";
  my $contents = param('contents') || " ";
  my $original_contents = $contents;
  my $URI = param('URI') || "";
  my $linkname = param('linkname') || "";
  my $parentid = param('parent') || $folderid;
  $expand = param('expand') || 1;
  $folder =~ s/\ /\%20/g;
  print br;
  print qq{<b>$text->{section_message_preview}</b>},br;
  print qq{<div class="fresh_title message_title"><b>$messagename</b></div>\n};
  print qq{<div class="fresh_body message_body">};
  print qq{<font class="navigation"><b>};
  print qq{$text->{dialog_by} $name</b></font>},br;

  print_contents(\$contents);
  # print_contents($contents);
  if ($URI) 
  {
    print qq{ [ <a href="$URI" target="_blank">};
    $linkname ? print qq{$linkname} : print qq{$URI};
    print qq{</a> ]};
  }
  print qq{</div>};
  print br;
  $messagename = $dbh->quote($messagename);
  $contents = $dbh->quote($contents);
  $URI = $dbh->quote($URI);
  print qq{<table border=0><tr><td bgcolor="#000000"><b>},
        qq{<font color="#cccccc" face="arial, helvetica,sans-serif">$text->{message_edit_dialog}</font>},
        qq{</b></td></tr></table>\n};
  print qq{<table border=1><tr><td>};
  print qq{$text->{message_title_dialog}: },textfield(-name=>"messagename", -value=>"$messagename"),br;
  print qq{$text->{message_body_dialog}:},br,textarea(-name=>"contents", -value=> "$original_contents", -rows=>"10",
        -columns=>"80", -wrap=>"virtual"),br;
  print qq{$text->{url_dialog} },textfield(-name=>"URI", -value=>"$URI", size=>50),br;
#  print qq{&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", -label=>$text->{button_label_preview});
  print submit(-name=>'op', -value=>"add entry", -label=>$text->{button_label_add_entry});
  print qq{</td></tr></table>};
  print end_form(),end_html();

# run plugins that go at the bottom of the page
  for (my $plugin=0; $plugin<@{$hooks->{'message_compose_bottom'}}; $plugin++) {
    &{$hooks->{'message_compose_bottom'}->[$plugin]};
  }
}
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=>"$settings->{'title_tag'} - $text->{page_title_error_no_subject}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                     -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                     -meta=>{'viewport'=>'width=device-width'},
                     -style=>{-src=>"$stylesheet"});
    print_title();
    print qq{<img src="$icon->{error}->[0]" width=$icon->{error}->[1] },
          qq{height=$icon->{error}->[2] border=0 alt="" align="left">};
    print qq{<font style="font-size: 170%; font-weight: bold; font-color: #AA0000; ">},
          qq{$text->{error_subject_line_required}</font>};
    print end_html();
    exit(0);
  }

  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=>"$settings->{'title_tag'} - $text->{page_title_error_no_body}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                     -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                     -meta=>{'viewport'=>'width=device-width'},
                     -style=>{-src=>"$stylesheet"});
    print_title();
    print qq{<img src="$icon->{error}->[0]" width=$icon->{error}->[1] },
          qq{height=$icon->{error}->[2] border=0 alt="" align="left">};
    print qq{<font style="font-size: 170%; font-weight: bold; font-color: #AA0000; ">},
          qq{$text->{warning_empty_body_not_allowed}</font>};
    print end_html();
    exit(0);
  }

  $messagename = $dbh->quote($messagename);
  $contents = $dbh->quote($contents);
  my $URI = param('URI') || "";
  $URI = '' if $URI eq 'http://';
  $URI = $dbh->quote($URI);
  my $linkname = param('linkname') || "";
  $linkname = $dbh->quote($linkname);
  my $parentid = int(param('parent')) || $folderid;
  my $folderid = int(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];
  $filename_store = $dbh->quote($filename_store);
  $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 $settings->{'user_table'}.id
                FROM user, members, message
                WHERE message.id=$folderid AND $settings->{'user_table'}.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 $settings->{'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")
    sysopen (OUTFILE,"$home_dir/re_files/$local_file_name", O_WRONLY|O_CREAT)
          || 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=>"$settings->{'title_tag'} - $text->{page_title_edit_entry}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  $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 qq{<center><h1>$text->{page_title_edit_entry}</h1></center>};
  unless ($our_userid == $message_ownerid || $our_userid == 1) 
  {
    print qq{$text->{warning_cannot_edit_unowned}},br;
  }
  elsif ( $message_age < $settings->{'edit_interval'} )
  {
    my $preview_contents = $message_contents;
    # $preview_contents =~ s/\n/<br>/gm if $preview_contents;
    $folder =~ s/\ /\%20/g if $folder;
    print qq{<table border=1><tr><td>};
    print qq{<table border=0 cellpadding=1><tr>},
          qq{<td class="fresh_title">},
          qq{<b>$message_name</b></p></td></tr>};
    print qq{<tr><td class="fresh_body">};
    print qq{<a href="$message_URI">link</a> - } if $message_URI;

    print_contents(\$preview_contents);
    # print_contents($preview_contents);
    
    print qq{</font>} if ( $our_userid < 1 );
    print qq{</p></tr></td></table>\n};
    print qq{</td></tr></table>};
    print qq{<table border=1><tr><td>};
    print qq{$text->{message_title_dialog}: },textfield(-name=>"messagename", -value=>"$message_name"),br;
    print qq{$text->{message_body_dialog}:},br,
          textarea(-name=>"contents",
                   -value=> "$message_contents",
                   -rows=>"15",
                   -columns=>"80",
                   -wrap=>"virtual"),br;
    print qq{$text->{url_dialog} },
          textfield(-name=>"URI", -value=>"$message_URI"),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="id" value="$id">};
    print qq{<input type="hidden" name="expand" value="$expand">};
    # print submit(-name=>'op', -value=>"preview edit");
    print submit(-name=>'op', -value=>"save changes", -label=>$text->{button_label_save_changes});
    print qq{</td></tr></table>};
    print end_form();
  }
  else
  {
    print qq{$text->{warning_message_older_than_edit_interval}};
  }
  print end_html();
}
elsif ($op eq 'save changes') {
  my $id = param('id');
  my $messagename = param('messagename') || " ";
  $messagename = $dbh->quote($messagename);
  my $contents = param('contents') || " ";
  $contents = $dbh->quote($contents);
  my $URI = param('URI') || "";
  $URI = $dbh->quote($URI);
  my $parentid = int(param('parent')) || $folderid;
  $expand = int(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=>"$settings->{'title_tag'} - $text->{page_title_create_account}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print qq{<p style="margin-top:0; margin-bottom:0; ">};
  print_title(); 
  print start_form(-action=>"$cgi_url", -method=>"post");
  print qq{<p style="margin-top:0; margin-bottom:0; ">};
  print qq{<table border=0>};
  print qq{<tr><td colspan=3>};
  print <<"  END_OF_HTML";
  <center><span style="font-size: 200%; ">
  $text->{page_title_create_account}
  </span></center>
  <br>$text->{dialog_request_account_policy}<br><br>
  END_OF_HTML
  print qq{</td></tr>};
  print qq{<tr><td align=right>$text->{dialog_login_name_label}:</td><td>},
        textfield(-name=>"user_name_app"),qq{</td></tr>};
  print qq{<tr><td valign=top align=right>$text->{dialog_email}:</td>},
        qq{<td valign=top >},textfield(-name=>"email_app").qq{</td><td>},
        qq{<span style="font-size: 90%; color: #aa0000; ">},
        qq{<b>*</b>$text->{dialog_warning_valid_email}</font></td></tr>};
  print qq{<tr><td valign=top align=right>$text->{dialog_email_verify}:</td>},
        qq{<td valign=top >},textfield(-name=>"email_app1").qq{</td><td><br><br></td></tr>};
  print qq{<tr><td valign=top align=right>$text->{dialog_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>$text->{dialog_warning_password}</font></td></tr>};
  print qq{<tr><td valign=top align=right>$text->{dialog_password_verify}:</td>},
        qq{<td valign=top >},password_field(-name=>"password_app1").qq{</td><td></td></tr>};
  print qq{<tr><td colspan=3><i>$text->{dialog_final_email_warning}<i></p></td></tr>};
  print qq{</table>};
  print qq{<table border=1 class="fresh_body" },
        qq{width="80%"><td>$settings->{'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=>"$settings->{'title_tag'} - $text->{page_title_create_account}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title(); 
  print qq{<center><h1>$text->{page_title_create_account}</h1></center>};
  print qq{<font color="#FF0000"><b>};
  print qq{$text->{dialog_warning_username_required}},br,br
          unless my $user_name_app = param('user_name_app');
  print qq{$text->{dialog_warning_username_bang_disallowed}},br,br if $user_name_app =~ /^!/;
  print qq{$text->{dialog_warning_email_required}},br,br unless my $email_app = param('email_app');
  print qq{$text->{dialog_warning_email_confirm_required}},br,br
          unless my $email_app1 = param('email_app1');
  print qq{$text->{dialog_warning_email_not_match}},br unless $email_app eq $email_app1;
  print qq{$text->{dialog_warning_password_required}},br,br
          unless my $password_app = param('password_app');
  print qq{$text->{dialog_warning_password_confirm_required}},br,br
          unless my $password_app1 = param('password_app1');
  print qq{$text->{dialog_warning_password_not_match}},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
  my $user_name_app_banged = $dbh->quote("\!$user_name_app");
  my $user_name_app_quoted = $dbh->quote($user_name_app);
  $query = qq{SELECT id FROM $settings->{'user_table'} WHERE name=$user_name_app_quoted or  name=$user_name_app_banged};
  $sth = $dbh->prepare ($query);
  my $rv = int ( $sth->execute() );
  print qq{$text->{dialog_warning_username_taken}},br if $rv > 0;
  my $stop_flag = 1 if $rv>0;

  # Check for existing e-mail address
  my $email_app_test = $dbh->quote($email_app);
  $query = qq{SELECT id FROM $settings->{'user_table'} WHERE email=$email_app_test};
  $sth = $dbh->prepare ($query);
  $rv = int ( $sth->execute() );
  print qq{$text->{dialog_warning_email_taken}},br if $rv > 0;
  $stop_flag = 1 if $rv>0;
  print qq{</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 $settings->{'user_table'} set name=$user_name_app_banged, email=$email_app_test,
                password="$pass_md5", passkey="$passkey"};
    my $sth = $dbh->prepare ($query);
    $sth->execute ();

    send_confirm_message("$user_name_app");

    # Print confirmation
    print qq{$text->{dialog_account_created_successfully}};
    print qq{[ <a href="$cgi_url">$text->{dialog_return_to} <b><i>$settings->{'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=>"$settings->{'title_tag'} - $text->{page_title_account_confirmation}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title(); 
  print qq{<center><h1>$text->{page_title_account_confirmation}</h1></center>};
  my $user_name_banged = $dbh->quote("!$user_name");
  my $query = qq{SELECT passkey, id FROM $settings->{'user_table'} WHERE name=$user_name_banged};
  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
    $user_name = $dbh->quote($user_name);
    $query = qq{UPDATE $settings->{'user_table'} SET name=$user_name, timestamp=timestamp WHERE id=$id};
    $sth = $dbh->prepare ($query);
    $sth->execute ();

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

    print qq{$text->{dialog_account_confirmed}  <a href="$cgi_url?op=login">$text->{dialog_login}</a>};

    # Send new account notification to site owner if requested
    if ($settings->{'notification_address'})
    {

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

    my $message = <<END_OF_MESSAGE;
$settings->{'title_tag'} $text->{notification_dialog_new_user}: $user_name ($email_app).
$settings->{'notification_message'}

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

$text->{notification_dialog_do_not_reply}
END_OF_MESSAGE

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

  }
  else
  {
    print qq{$text->{dialog_error_confirmation_somethingA}},
          qq{<a href="mailto:$settings->{'notification_address'}">$settings->{'notification_address'}</a>},
          qq{$text->{dialog_error_confirmation_somethingB}},br,br;
  }
  $sth->finish ();
}
elsif ( $op eq 'mail password') {
  my $email = param('email');
  my $query_email = $dbh->quote($email);
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$settings->{'title_tag'} - $text->{page_title_sending_password}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title(); 
  print qq{<center><h1>$text->{page_title_sending_password}</h1></center>};

  # grab username and password from user table
  $query = qq{SELECT name, password FROM $settings->{'user_table'} WHERE email=$query_email AND id>1};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($name, $password) = $sth->fetchrow_array ();

  if ($password eq 'deactivate') {
    print qq{$text->{dialog_warning_account_deactivated}\n};
    $sth->finish ();
    $dbh->disconnect ();
    exit(0);
    }
  elsif ( $name eq '' ) {
    # do nothing if no user was found....
  }
  else {
    # generate password

    my @chars = ( "A" .. "Z", "a" .. "z", 0 .. 9, "+", "-", "=", "*", "#", "&", ".", ";", ",", "_" );
    $password = join("", @chars[ map { rand @chars } (1 .. 6) ]);

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

  if ($password) {
    my $temp_domain_name = $settings->{'domain_name'};
    $temp_domain_name =~ s/^\.//; # strip leading '.' from beginning of domain name if present.
# do some text substitutions
    $text->{email_new_password_text} =~ s/%PASSWORD/$password/g;
    $text->{email_new_password_text} =~ s/%NAME/$name/g;
    $text->{email_new_password_text} =~ s/%TITLE/$settings->{'title_tag'}/g;
    $text->{email_new_password_text} =~ s/%NOTICE/$text->{notification_dialog_do_not_reply}/g;
    $text->{email_new_password_text} =~ s#%URL#http://$temp_domain_name#g;

    my $mailer = Mail::Mailer->new();
    $mailer->open({'From' => "$settings->{'notification_address'}",
                   'To' => "$email",
                   'Subject' => "Your $settings->{'title_tag'} login"} );
    print $mailer "$text->{email_new_password_text}";
    close($mailer);
# do some text substitutions
    $text->{dialog_new_password_sent} =~ s/%CGI_URL/$cgi_url/;
    print $text->{dialog_new_password_sent};
  }
  else
  {
    print qq{$text->{dialog_warning_no_account_for_email}},br;
  }
}
elsif ( $op eq 'account maintenance' && $name ) {
  $expand = param('expand') || $thread_style;
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$settings->{'title_tag'} - $text->{page_title_account_maintenance}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title();
  print qq{<center><h1>$text->{page_title_account_maintenance} - $name</h1></center>};
  print start_form(-action=>$cgi_url, -method=>"post");
  if( $name ) {
    $text->{logged_in_msg} =~ s/\%NAME/$name/g;
    print qq{$text->{logged_in_msg} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;};
    print submit(-name=>"op", -value=>"logout"),br;
    }
  else {
    print qq{<a href="$cgi_url?op=login">$text->{dialog_login}</a>},br;
    }
  print qq{$text->{dialog_return_to}: <a href=$cgi_url?folder=$folder><b>$folder</b></a>},br,br;

  print qq{<font size=+1><b>$text->{dialog_change_password}:</b></font>},br;
  print qq{<table cellpadding=0 cellspacing=0>};
  print qq{<tr><td align=right>$text->{dialog_new_password}:</td><td>},
        password_field(-name=>"pass1", -value=>''),"</td></tr>";
  print qq{<tr><td align=right>$text->{dialog_retype_password}:</td><td>},
        password_field(-name=>"pass2", -value=>''),"</td></tr>";
  print qq{<tr><td align=right></td><td>},
        submit(-name=>"op", -value=>"$text->{button_change_password}"),"</td></tr>";
  print qq{</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 qq{<font size=+1><b>$text->{dialog_change_email}:</b></font>},br;
  print qq{[ $text->{dialog_current_email}: <b>$user_email</b> ]},br;
  print qq{<table cellpadding=0 cellspacing=0>};
  print qq{<tr><td align=right>$text->{dialog_new_email}:</td><td>},
        textfield(-name=>"email1", -value=>''),qq{</td></tr>};
  print qq{<tr><td align=right>$text->{dialog_retype_new_email}:</td><td>},
        textfield(-name=>"email2", -value=>''),"</td></tr>";
  print qq{<tr><td align=right></td><td>},
        submit(-name=>"op", -value=>"$text->{button_change_email}"),"</td></tr>";
  print qq{</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>$text->{dialog_user_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> = "$text->{dialog_thread_accept_default}";},br,
        qq{<b>1</b> = "$text->{dialog_thread_explain_style_1}";},br,
        qq{<b>2</b> = "$text->{dialog_thread_explain_style_2}";},br,
        qq{<b>3</b> = "$text->{dialog_thread_explain_style_3}";},br,
        qq{<b>4</b> = "$text->{dialog_thread_explain_style_4}";},br,
        qq{<b>5</b> = "$text->{dialog_thread_explain_style_5}"},br,
        qq{</font>};
  print submit(-name=>"op", -value=>"set thread-style", -label=>$text->{button_label_set_thread_style});
  print  qq{</td></tr></table>},br;

  print qq{<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=>"$settings->{'title_tag'} - $text->{page_title_set_thread_pref}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title();
  print qq{$text->{dialog_return_to}: <a href=$cgi_url?folder=$folder><b>$folder</b></a>},br;
  print qq{$text->{dialog_return_to}: <a href=$cgi_url?folder=$folder&op=account%20maintenance><b>}.
        qq{$text->{page_title_account_maintenance}</b></a>},br;
  $query = qq{UPDATE $settings->{'user_table'} SET thread='$thread_pref', timestamp=timestamp WHERE id=$our_userid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  print br,qq{$text->{dialog_notice_thread_set} $thread_pref.},br;
  }
elsif ( $op eq 'change password' && $name ) {
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$settings->{'title_tag'} - $text->{page_title_password_changed}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title();
  print qq{$text->{dialog_return_to}: <a href=$cgi_url?folder=$folder><b>$folder</b></a>},br;
  print qq{$text->{dialog_return_to}: <a href=$cgi_url?folder=$folder&op=account%20maintenance><b>}.
        qq{$text->{page_title_account_maintenance}</b></a>},br;
  my $pass1 = param('pass1');
  my $pass2 = param('pass2');
  unless ($pass1 && $pass2) {
    print qq{$text->{dialog_warning_enter_password_twice}},br; 
    }
  elsif ($pass1 && ($pass1 eq $pass2) ) {
    unless ( $pass1 =~ /^[\w .-_?~]+$/ ) {
      print header(-cookie=>[$cookie]),"$text->{dialog_warning_illegal_char_in_password}";
      }
    else {
      my $pass_md5 = md5_hex($pass1);
      $query = qq{UPDATE $settings->{'user_table'} SET password='$pass_md5', timestamp=timestamp WHERE id=$our_userid};
      $sth = $dbh->prepare ($query);
      $sth->execute ();
      print br,"$text->{dialog_notice_password_updated}",br;
      }
    }
  else {
    print br,"$text->{dialog_warning_passwords_must_match}",br;
    }
  }
elsif ( $op eq 'change e-mail address') {
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$settings->{'title_tag'} - $text->{page_title_change_email}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title();
  print qq{$text->{dialog_return_to}: <a href=$cgi_url?folder=$folder><b>$folder</b></a>},br;
  print qq{$text->{dialog_return_to}: <a href=$cgi_url?folder=$folder&op=account%20maintenance><b>}.
        "$text->{page_title_account_maintenance}</b></a>",br;
  my $email1 = param('email1');
  my $email2 = param('email2');
  unless ($email1 && $email2) {
    print qq{$text->{dialog_warning_must_enter_email_twice}},br; 
    }
  elsif ($email1 && ($email1 eq $email2) ) {
    unless ( $email1 =~ /^[\w .-_?~@]+$/ ) {
      print header(-cookie=>[$cookie]),"$text->{dialog_warning_email_illegal_char}";
      }
    else {
      $query = qq{UPDATE $settings->{'user_table'} SET email='$email1', timestamp=timestamp WHERE id=$our_userid};
      $sth = $dbh->prepare ($query);
      $sth->execute ();
      print br,"$text->{dialog_notice_email_changed}",br;
      }
    }
  else {
    print br,"$text->{dialog_warning_emails_must_match}",br;
    }
  }
elsif ( $op eq 'bury' && $name eq 'root') {
  my $messageid = int(param('id'));
  my $root_message_id = param('root');
  my $confirm = param('confirm');
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$settings->{'title_tag'} - $text->{page_title_message_disapear}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title(); 
  print qq{<center><h1>$text->{page_title_message_disapear}: $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">$text->{dialog_link_dont_delete}</a>},br,br;
    # Grab the message info for the message to be displayed
    $query = qq{ SELECT message.name, contents, URI, linkname, $settings->{'user_table'}.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 qq{<table border=1><tr><td>};
    print qq{<table border=0 cellpadding=3 cellspacing=1>};
    print qq{<tr><td><b>$message_name</b></td></tr>};
    print qq{<tr><td>};
    print qq{<font size=-1><b>};
    print qq{$text->{dialog_by} $message_user},br;
    print qq{</b></font>};
    print qq{<a href=$URI>link</a> - } if $URI;
    my $old_contents =~ s/\r/<br \/>/g;
    $folder =~ s/\ /%20/g;
    print qq{$message_contents},br;
    print qq{</td></tr></table>};
    print qq{</td></tr></table>};

    print qq{[ <a href="$cgi_url?folder=$folder&op=bury&id=$messageid&},
          qq{root=$root_message_id&confirm=Y">$text->{dialog_link_confirm_disapear}</a> ] },br;
    print qq{$text->{dialog_explain_disapear}},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">$text->{dialog_link_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 = "$text->{dialog_user_sort_order_username}",br;
  }
  elsif ( $sort_order eq "B" ) # sort by username, DESC
  {
    $order = "user.name DESC";
    $order_description = "$text->{dialog_user_sort_order_username_desc}",br;
  }
  elsif ( $sort_order eq "C" ) # sort by last on date/time DESC
  {
    $order = "user.timestamp DESC";
    $order_description = "$text->{dialog_user_sort_order_access_desc}",br;
  }
  elsif ( $sort_order eq "D" ) # sort by last on date/time ASC
  {
    $order = "user.timestamp ASC";
    $order_description = "$text->{dialog_user_sort_order_access_asc}",br;
  }
  elsif ( $sort_order eq "E" ) # sort by last message post DESC
  {
    $order = "last_message DESC";
    $order_description = "$text->{dialog_user_sort_order_last_message_desc}",br;
  }
  elsif ( $sort_order eq "F" ) # sort by last message post ASC
  {
    $order = "last_message ASC";
    $order_description = "$text->{dialog_user_sort_order_last_message_asc}",br;
  }
  elsif ( $sort_order eq "G" ) # sort by number of posts DESC
  {
    $order = "messagecount DESC";
    $order_description = "$text->{dialog_user_sort_order_message_count_desc}",br;
  }
  elsif ( $sort_order eq "H" ) # sort by number of posts ASC
  {
    $order = "messagecount ASC";
    $order_description = "$text->{dialog_user_sort_order_message_count_asc}",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 = "$text->{dialog_user_filter_all}",br;
  }
  elsif ( $filter eq "B" ) # show only active user accounts
  {
    $sql_filter = qq{AND $settings->{'user_table'}.password != "$deactive_pass"};
    $filter_description = "$text->{dialog_user_filter_only_active}",br;
  }
  elsif ( $filter eq "C" ) # show only deactivated user accounts
  {
    $sql_filter = qq{AND $settings->{'user_table'}.password = "$deactive_pass"};
    $filter_description = "$text->{dialog_user_filter_only_inactive}",br;
  }
  else # default to "A"
  {
    $filter = 'A';
    $sql_filter = "";
    $filter_description = "$text->{dialog_user_filter_all}",br;
  }

  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$settings->{'title_tag'} - $text->{page_title_user_administration}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title(); 
  print qq{<center><h1>$text->{page_title_user_administration}</h1></center>};

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

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

  $query = qq{SELECT $settings->{'user_table'}.id, $settings->{'user_table'}.name, $settings->{'user_table'}.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,
                     $settings->{'user_table'}.password,
                     MAX(message.time) as last_message
              FROM user
                LEFT JOIN message ON $settings->{'user_table'}.id=message.userid AND message.folder='N'
              WHERE $settings->{'user_table'}.id > 1
                    # AND message.folder='N'
                    $sql_filter
              GROUP BY $settings->{'user_table'}.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">$text->{dialog_return_to} <b><i>$settings->{'title_tag'}</i></b></a> ]},br,br;
    print qq{$text->{dialog_users}: <i>$order_description</i>};

    # user filtering options
    print qq{<font class="navigation">$text->{dialog_user_account_filter_options}: <b>};
    print qq{[ <a href="$cgi_url?op=user%20administration&sort=$sort_order&filter=A">},
          qq{$text->{dialog_user_account_filter_show_all}</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{$text->{dialog_user_account_filter_show_active}</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{$text->{dialog_user_account_filter_show_inactive}</a> ]} unless $filter eq 'C';
    print qq{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;};
    print qq{[ $text->{dialog_users}: $active_user_count $text->{dialog_label_active}/<font },
          qq{style="color: #AA0000; ">$deactive_user_count $text->{dialog_label_inactive}</font> ]};
    print qq{</b></font>};

    # create the user sumary table
    print qq{<table border=1 class="navigation">};
    print qq{<tr class="fresh_title">};
    print qq{<th align="center">$text->{dialog_label_username} },
          qq{<font style="font-size: 60%; ">},
          qq{(<a href="$cgi_url?op=user%20administration&sort=A&filter=$filter">$text->{dialog_label_ascending}</a>},
          qq{/},
          qq{<a href="$cgi_url?op=user%20administration&sort=B&filter=$filter">$text->{dialog_label_descending}</a>)},
          qq{</font></th>};
    print qq{<th align="center">$text->{dialog_column_label_email}</th>};
    print qq{<th align="center">$text->{dialog_column_label_posts} },
          qq{<font style="font-size: 60%; ">},
          qq{(<a href="$cgi_url?op=user%20administration&sort=H&filter=$filter">$text->{dialog_label_ascending}</a>},
          qq{/},
          qq{<a href="$cgi_url?op=user%20administration&sort=G&filter=$filter">$text->{dialog_label_descending}</a>)},
          qq{</font></th>};
    print qq{<th align="center">$text->{dialog_column_label_last_post} },
          qq{<font style="font-size: 60%; ">},
          qq{(<a href="$cgi_url?op=user%20administration&sort=F&filter=$filter">$text->{dialog_label_ascending}</a>},
          qq{/},
          qq{<a href="$cgi_url?op=user%20administration&sort=E&filter=$filter">$text->{dialog_label_descending}</a>)},
          qq{</font></th>};
    print qq{<th align="center">$text->{dialog_column_label_last_access} },
          qq{<font style="font-size: 60%; ">},
          qq{(<a href="$cgi_url?op=user%20administration&sort=D&filter=$filter">$text->{dialog_label_ascending}</a>},
          qq{/},
          qq{<a href="$cgi_url?op=user%20administration&sort=C&filter=$filter">$text->{dialog_label_descending}</a>)},
          qq{</font></th>};
    print qq{<th align="center">$text->{dialog_column_label_account_status}</th>};
    print qq{<th align="center">$text->{dialog_column_label_user_group_count}</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 class="fresh_title">}; # start table row
        $alternate_line_color = 'off'; # turn off "green bar" for next line
      }
      else 
      {
        print qq{<tr class="fresh_body">}; # 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="$text->{link_comment_manage_user}: $manage_username">}, #"
            qq{$manage_username</a></td>}; # print username
      print qq{<td><a href="mailto:$manage_email" },
            qq{title="$text->{link_comment_email_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">$text->{dialog_user_status_deactivated}</font></td>}
      }
      elsif ( $manage_username =~ /^\!/ )
      {
         $manage_username =~ s/^\!//;
         print qq{<td><a href="$cgi_url?op=resend%20confirmation&user_name_app=$manage_username">},
                qq{$text->{dialog_link_resend_confirmation_message}</a></td>};
      }
      else
      {
         print qq{<td>$text->{dialog_user_status_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">$text->{dialog_link_jump_to_group_admin}},
        qq{</a> ]},br.qq{ [ <a href="$cgi_url">$text->{dialog_return_to} <b><i>$settings->{'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=>"$settings->{'title_tag'} - $text->{page_title_user_administration}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title(); 

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

  print qq{<center><h1>$text->{page_title_user_administration} -- $manage_username</h1></center>};

  print start_form(-action=>$cgi_url, -method=>"post");
  print qq{<b>$text->{dialog_label_user_name}: $manage_username }
        if $manage_userid != 1; # root's name can't change
  print submit(-name=>'op', -value=>"$text->{button_label_change_username}"),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 qq{<table cellpadding=0 cellspacing=0>};
  print qq{<tr><td align=right>$text->{dialog_label_new_password}:</td><td>},
        password_field(-name=>"pass1", -value=>''),qq{</td></tr>};
  print qq{<tr><td align=right>$text->{dialog_label_retype_password}:</td><td>},
        password_field(-name=>"pass2", -value=>''),qq{</td></tr>};
  print qq{</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>$text->{dialog_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>$text->{dialog_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 $settings->{'user_table'} WHERE id=$manage_userid};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  my ($last_access_time) = $sth->fetchrow_array ();
  $sth->finish ();
  print qq{<b>$text->{dialog_last_access_time}:</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 qq{<b>$text->{dialog_select_user_groups}</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', -label=>$text->{button_label_update_user}),br;

  # if user has not created any messages, they can safely be deleted
  if ($message_count == 0) {
    print qq{$text->{dialog_notice_okay_to_delete_user}},br;
    print submit(-name=>"op", -value=>"delete user", -label=>$text->{button_label_delete_user})," ";
    print checkbox(-name=>"confirm delete", -value=>"confirm delete",
                   -label=>$text->{checkbox_label_confirm_delete}),br;
    }

  print br,qq{[ <a href="$cgi_url?op=user%20administration">$text->{dialog_link_back_to_user_admin}</a> ]},br;
  print qq{[ <a href="$cgi_url?op=group%20administration">$text->{dialog_link_jump_to_group_admin}</a> ]},br;
  print qq{ [ <a href="$cgi_url">$text->{dialog_return_to} <b><i>$settings->{'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=>"$settings->{'title_tag'} - $text->{page_title_change_username}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title(); 

  if ( $manage_userid != 1 ) { # root's name can't change
    print qq{<center><h1>$text->{page_title_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{$text->{dialog_label_username}: };
    print textfield(-name=>'change_username', -size=>30, -value=>"$manage_username"),br,br;
    print qq{<b>$text->{dialog_warning_username_change_notice}</b>},br;
    print checkbox(-name=>"confirm username change", -value=>"confirm username change",
                   -label=>$text->{checkbox_label_confirm_name_change}),br;
    print submit(-name=>"op", -value=>"complete username change",
                 -label=>$text->{button_label_comple_name_change}),br;
    print end_form(), end_html();
    }
  else {
    print qq{<center><h1>$text->{page_title_change_username} -- $text->{dialog_error_change_root_name}</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 $settings->{'user_table'} 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=>"$settings->{'title_tag'} - $text->{page_title_change_username}",
                     -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                     -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                     -meta=>{'viewport'=>'width=device-width'},
                     -style=>{-src=>"$stylesheet"});
    print_title(); 

    print qq{<center><h1>$text->{dialog_error_cannot_change_root_username}</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=>"$settings->{'title_tag'} - $text->{page_title_name_change_failed}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -style=>{-src=>"$stylesheet"});
    print_title(); 

    $text->{dialog_warning_username_in_use} =~ s/\%NAME/$change_username/g;;
    print qq{<center><h1>$text->{page_title_name_change_failed} -- },
          qq{$text->{dialog_warning_username_in_use}</h1></center>},br,br;
    print qq{$text->{dialog_instructions_go_back_username}},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=>"$settings->{'title_tag'} - $text->{page_title_name_change_not_complete}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
    print_title(); 

    print qq{<center><h1>$text->{dialog_error_name_change_confirm_required}</h1></center>},br,br;
    print qq{$text->{dialog_instructions_go_back_username_confirm}},br,br;
    }
  # otherwise...
  else {
    # update the user to the new name
    $query = qq{UPDATE $settings->{'user_table'} 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 start_html(-title=>"$settings->{'title_tag'} - $text->{page_title_change_username_complete}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
    print_title(); 

    print qq{<center><h1>$text->{page_title_change_username_complete} -- $change_username</h1></center>},br,br;
    print qq{$text->{dialog_notice_username_changed}},br,br;
    print qq{$text->{dialog_notice_name_change_notification_sent} ($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 = $settings->{'domain_name'};
    $temp_domain_name =~ s/^\.//; # strip leading '.' from beginning of domain name if present.

    $text->{email_notice_username_change} =~ s/%TITLE/$settings->{'title_tag'}/g;
    $text->{email_notice_username_change} =~ s/%NEW_NAME/$change_username/g;
    $text->{email_notice_username_change} =~ s#%URL#http://$temp_domain_name#g;
    $text->{email_notice_username_change_subject} =~ s/%TITLE/$settings->{'title_tag'}/g;

    my $mailer = Mail::Mailer->new();
    $mailer->open({'From' => "$settings->{'notification_address'}",
                   'To' => "$manage_email",
                   'Subject' => $text->{email_notice_username_change_subject}} );
    print $mailer "$text->{email_notice_username_change}";
    close($mailer);
    }
  print qq{[ <a href="$cgi_url?op=user%20administration">$text->{dialog_link_back_to_user_admin}</a> ]},br;
  print qq{ [ <a href="$cgi_url">$text->{dialog_return_to} <b><i>$settings->{'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 $settings->{'user_table'} 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=>"$settings->{'title_tag'} - $text->{page_title_user_delete_failed}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
    print_title(); 
    print qq{<center><h1>$text->{page_title_user_delete_failed}: $manage_username</h1></center>};
    print br,"$text->{dialog_error_notice_problem_deleting_user}",br;
    print $text->{dialog_error_notice_problem_deleting_user2},br,br
          if $confirm_delete ne 'confirm delete';

    print qq{[ <a href="$cgi_url?op=user%20administration">$text->{dialog_link_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=>"$settings->{'title_tag'} - $text->{page_title_user_administration}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title(); 
  print qq{<center><h1>$text->{page_title_user_administration} -- $manage_username</h1></center>};

  $query = qq{UPDATE $settings->{'user_table'} 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 = $settings->{'domain_name'};
    $temp_domain_name =~ s/^\.//; # strip leading '.' from beginning of domain name
                                  # if present.

    $text->{email_notice_account_update} =~ s/%TITLE/$settings->{'title_tag'}/g;
    $text->{email_notice_account_update} =~ s/%USER_NAME/$manage_username/g;
    $text->{email_notice_account_update} =~ s/%GROUP_NAME_LIST/$new_group_names/g;
    $text->{email_notice_account_update} =~ s#%URL#http://$temp_domain_name#g;
    $text->{email_notice_account_update_subject} =~ s/%TITLE/$settings->{'title_tag'}/g;
    my $mailer = Mail::Mailer->new('sendmail');
    $mailer->open({'From' => "$settings->{'notification_address'}",
                   'To' => "$manage_email",
                   'Subject' => $text->{email_notice_account_update_subject}} );
    print $mailer "$text->{email_notice_account_update}";
    close($mailer);

  if ($pass1) {
    unless ($pass1 && $pass2) {
      print qq{$text->{dialog_warning_enter_password_twice}},br; 
      }
    elsif ($pass1 && ($pass1 eq $pass2) ) {
      unless ( $pass1 =~ /^[\w .-_?~]+$/ ) {
        print header(-cookie=>[$cookie]),"$text->{dialog_warning_illegal_char_in_password}";
        }
      else {
        my $pass_md5 = md5_hex($pass1);
        $query = qq{UPDATE $settings->{'user_table'} SET password='$pass_md5', timestamp=timestamp WHERE id=$manage_userid};
        $sth = $dbh->prepare ($query);
        $sth->execute ();
        print br,"<b>$manage_username</b>: $text->{dialog_notice_password_updated}",br;
        }
      }
    else {
      print br,"$text->{dialog_warning_password_not_match}",br;
      }
    }

  print qq{$text->{dialog_notice_change_made_to_user}: $manage_username.},br;
  print br;
  print qq{[ <a href="$cgi_url?op=user%20administration">$text->{dialog_link_back_to_user_admin}</a> ]},br;
  print qq{[ <a href="$cgi_url?op=group%20administration">$text->{dialog_link_jump_to_group_admin}</a> ]},br;
  print qq{ [ <a href="$cgi_url">$text->{dialog_return_to} <b><i>$settings->{'title_tag'}</i></b></a> ]};
  $sth->finish ();
  }
elsif ( $op eq 'group administration' && $name eq 'root') {
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$settings->{'title_tag'} - $text->{page_title_group_admin}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title(); 
  print qq{<center><h1>$text->{page_title_group_admin}</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 qq{<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 qq{</td><td valign=top>};
  print qq{$text->{dialog_label_create_new_group}:},br;
  print textfield(-name=>'new group name', -value=>'', size=>20);
  print submit(-name=>'op', -value=>'create this group'),br;
  print qq{</td></tr></table>};
  print qq{ [ <a href="$cgi_url">$text->{dialog_return_to} <b><i>$settings->{'title_tag'}</i></b></a> ]};
  print qq{ [ <a href="$cgi_url?op=user%20administration">$text->{page_title_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=>"$settings->{'title_tag'} - $text->{page_title_group_admin} - $text->{dialog_label_create_new_group}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title(); 
  print qq{<center><h1>$text->{page_title_group_admin}</h1><b>$text->{dialog_label_create_new_group}</b></center>};

  if ($new_group_name eq 'NULL') { # if no name given, give error and link back.
    print qq{<h3><font color="#AA0000">$text->{dialog_error_no_group_name}</font></h3>};
    print qq{[ <a href="$cgi_url?op=group%20administration">$text->{dialog_link_back_to_user_admin}</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 qq{<b>$new_group_name</b> $text->{dialog_error_group_name_used}},br;
        print qq{[ <a href="$cgi_url?op=group%20administration">$text->{dialog_link_back_to_group_admin}},
              qq{</a> ]},br;
        }
      else { # otherwise, create the new group and present link to edit group
        $new_group_name = $dbh->quote($new_group_name);
        $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 qq{$text->{dialog_label_new_group}: <b>$new_group_name</b> $text->{dialog_notice_group_created}},br;
        print submit(-name=>"op", -value=>"manage this group", -label=>$text->{button_label_manage_group});
        print hidden(-name=>"manage_groupid", value=>"$new_group_id"),br;
        print qq{ [ <a href="$cgi_url?op=group%20administration">$text->{dialog_link_back_to_group_admin}},
              qq{</a> ]},br;
        print qq{ [ <a href="$cgi_url?op=user%20administration">$text->{page_title_user_administration}</a> ]},br;
        print qq{[ <a href="$cgi_url">$text->{dialog_return_to} <b><i>$settings->{'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=>"$settings->{'title_tag'} - $text->{page_title_group_admin} -- managing group",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  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 qq{<center><h1>$text->{page_title_group_admin} -- $manage_groupname</h1></center>};

  # Get a list of user names and ids
  $query = qq{SELECT id, name FROM $settings->{'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{<font class="fresh_title" style="font-size: 75%; padding: 3px; ">},
        qq{$text->{dialog_members_in_group}: $rv</font>},br;
  print start_form(-action=>$cgi_url, -method=>"post");
  print qq{<input type="hidden" name="manage_groupid" value="$manage_groupid" />};
  print qq{<input type="hidden" name="manage_groupname" value="$manage_groupname" />};
  print qq{$text->{select_users_in_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', -label=>$text->{button_label_update_group})," ";
  print submit(-name=>'op', -value=>'delete group', -label=>$text->{button_label_delete_group})," ";
  print checkbox(-name=>"confirm delete", -value=>"confirm delete", label=>$text->{checkbox_label_confirm_delete}),br,br;
  print qq{[ <a href="$cgi_url?op=group%20administration">$text->{dialog_link_back_to_group_admin}</a> ]},br;
  print qq{[ <a href="$cgi_url?op=user%20administration">$text->{dialog_link_jump_to_user_admin}</a> ]},br;
  print qq{[ <a href="$cgi_url">$text->{dialog_return_to} <b><i>$settings->{'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=>"$settings->{'title_tag'} - $text->{page_title_group_admin} -- $text->{dialog_update} $manage_groupname",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title(); 
  print qq{<center><h1>$text->{page_title_group_admin} -- $text->{dialog_update} $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 qq{$text->{dialog_changes_made_to_group} $manage_groupname},br;
  print br;
  print qq{[ <a href="$cgi_url?op=group%20administration">$text->{dialog_link_jump_to_group_admin}</a> ]},br;
  print qq{[ <a href="$cgi_url?op=user%20administration">$text->{dialog_link_jump_to_user_admin}</a> ]},br;
  print qq{ [ <a href="$cgi_url">$text->{dialog_return_to} <b><i>$settings->{'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=>"$settings->{'title_tag'} - $text->{page_title_group_admin} -- $text->{dialog_delete} $manage_groupname",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print_title(); 
  print qq{<center><h1>$text->{page_title_group_admin} -- $text->{dialog_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 qq{$manage_groupname $text->{delete}},br;
    print qq{<a href="$cgi_url?op=group%20administration">$text->{dialog_link_back_to_group_admin}</a>},br;
  }
  else 
  {
    print qq{<font color="#AA0000"><b>$text->{dialog_error_confirm_required_group_delete}</b></font>},br;
    print qq{<a href="$cgi_url?op=manage%20this%20group&manage_groupid=$manage_groupid">},
          qq{$text->{dialog_link_return_to_group_management}</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=>"$settings->{'title_tag'} - $text->{page_title_attachment_administration}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print start_form(-action=>$cgi_url, -method=>"post");
  print qq{<p style="font-size:90%; ">};
  print_title(); 
  print qq{<p style="font-size:200%; font-weight: bold; text-align: center; margin-top: 1em; },
        qq{margin-bottom: 1em; ">$text->{page_title_attachment_administration}</p>\n};

  print qq{<p style="font-size:90%; ">};
  print qq{ [ <a href="$cgi_url">$text->{dialog_return_to} <i>$settings->{'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 = $text->{dialog_attach_sort_added_asc};
    }
    elsif ( $sort_order eq "B" ) # sort by id -- DESC
    {
      $order = "attachment.id DESC";
      $order_description = $text->{dialog_attach_sort_added_desc};
    }
    elsif ( $sort_order eq "C" ) # sort by last access -- ASC
    {
      $order = "last_access ASC";
      $order_description = $text->{dialog_attach_sort_access_asc};
    }
    elsif ( $sort_order eq "D" ) # sort by last access -- DESC
    {
      $order = "last_access DESC";
      $order_description = $text->{dialog_attach_sort_access_desc};
    }
    elsif ( $sort_order eq "E" ) # sort by username -- ASC
    {
      $order = "user.name ASC";
      $order_description = $text->{dialog_attach_sort_user_asc};
    }
    elsif ( $sort_order eq "F" ) # sort by username -- DESC
    {
      $order = "user.name DESC";
      $order_description = $text->{dialog_attach_sort_user_desc};
    }
    elsif ( $sort_order eq "G" ) # sort by filename -- ASC
    {
      $order = "attachment.filename ASC";
      $order_description = $text->{dialog_attach_sort_filename_asc};
    }
    elsif ( $sort_order eq "H" ) # sort by filename -- DESC
    {
      $order = "attachment.filename DESC";
      $order_description = $text->{dialog_attach_sort_filename_desc};
    }
    elsif ( $sort_order eq "I" ) # sort by folder name -- ASC
    {
      $order = "folder.name ASC";
      $order_description = $text->{dialog_attach_sort_foldername_asc};
    }
    elsif ( $sort_order eq "J" ) # sort by folder name -- DESC
    {
      $order = "folder.name DESC";
      $order_description = $text->{dialog_attach_sort_foldername_desc};
    }
    else
    {
      $order = "attachment.id ASC";
      $order_description = $text->{dialog_attach_sort_added_asc};
    }

    print qq{$text->{dialog_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{$text->{dialog_link_show_deleted}</a> ]};
      }
    else {
      print qq{[ <a href="$cgi_url?op=attachment%20administration&sort=$sort_order&hide=yes">};
      print qq{$text->{dialog_link_hide_deleted}</a> ]};
      }
# set up the attachment management table 
    print qq{<table border=1>};
    print qq{<tr class="fresh_title">};
    print qq{<th>$text->{table_column_head_id}</th>};
    print qq{<th>$text->{table_column_head_filename} },
          qq{<font style="font-size: 60%; ">},
          qq{(<a href="$cgi_url?op=attachment%20administration&sort=G&hide=$hide">$text->{dialog_label_ascending}</a>},
          qq{/},
          qq{<a href="$cgi_url?op=attachment%20administration&sort=H&hide=$hide">$text->{dialog_label_descending}</a>)},
          qq{</font></th>};
    print qq{<th>$text->{table_column_head_filesize}</th>};
    print qq{<th>$text->{table_column_head_user} },
          qq{<font style="font-size: 60%; ">},
          qq{(<a href="$cgi_url?op=attachment%20administration&sort=E&hide=$hide">$text->{dialog_label_ascending}</a>},
          qq{/},
          qq{<a href="$cgi_url?op=attachment%20administration&sort=F&hide=$hide">$text->{dialog_label_descending}</a>)},
          qq{</font></th>};
    print qq{<th>$text->{table_column_head_folder} },
          qq{<font style="font-size: 60%; ">},
          qq{(<a href="$cgi_url?op=attachment%20administration&sort=I&hide=$hide">$text->{dialog_label_ascending}</a>},
          qq{/},
          qq{<a href="$cgi_url?op=attachment%20administration&sort=J&hide=$hide">$text->{dialog_label_descending}</a>)},
          qq{</font></th>};
    print qq{<th>$text->{table_column_head_date_added} },
          qq{<font style="font-size: 60%; ">},
          qq{(<a href="$cgi_url?op=attachment%20administration&sort=A&hide=$hide">$text->{dialog_label_ascending}</a>},
          qq{/},
          qq{<a href="$cgi_url?op=attachment%20administration&sort=B&hide=$hide">$text->{dialog_label_descending}</a>)},
          qq{</font></th>};
    print qq{<th>$text->{table_column_head_last_access} },
          qq{<font style="font-size: 60%; ">},
          qq{(<a href="$cgi_url?op=attachment%20administration&sort=C&hide=$hide">$text->{dialog_label_ascending}</a>},
          qq{/},
          qq{<a href="$cgi_url?op=attachment%20administration&sort=D&hide=$hide">$text->{dialog_label_descending}</a>)},
          qq{</font></th>};
    print qq{<td></td}; # "select" boxes
    print qq{</tr>\n};

    # gather attachment information
    $query = qq{SELECT attachment.id,
                       $settings->{'user_table'}.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 $settings->{'user_table'}.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 class="fresh_body">}; # 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; ">* },
          qq{$text->{dialog_notice_attachment_actions_require_confirm}</font>},br;
    print qq{<table><tr>};
    print qq{<td align="right"><b>$text->{dialog_attachment_action}:</b> </td><td>};
    print radio_group(-name => 'attachment action',
                      -values => ['No Action', 'delete', 'archive-iso', 'archive-tar', 'archive-zip'],
                      -labels => $text->{radio_button_labels_attachment_actions},
                      -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>$text->{dialog_confirmation}:</b> </td><td>};
    print radio_group(-name => 'action confirmation',
                      -values => ['No Action', 'delete', 'archive-iso', 'archive-tar', 'archive-zip'],
                      -labels => $text->{radio_button_labels_attachment_actions},
                      -default => 'No Action'),br;
    print qq{</td></tr></table>};
    print submit(-name=>'op', -value=>'Proceed with archive', -label=>$text->{button_label_archive_proceed}),br;
    print qq{ [ <a href="$cgi_url">$text->{dialog_return_to} <i>$settings->{'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 qq{$text->{dialog_notice_archive_volume_in_place} -- <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">$text->{dialog_attachments_remove} },
          qq{$archive_name[0]</a>},br;
    print qq{<a href="$cgi_url?op=dlarchive">$text->{dialog_attachments_download} $archive_name[0]</a> ($archive_size)},br;
    print qq{<a href="$cgi_url?op=delarchive">$text->{dialog_attachments_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=>"$settings->{'title_tag'} - $text->{page_title_attachment_administration}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print start_form(-action=>$cgi_url, -method=>"post");
  print qq{<p style="font-size:90%; ">};
  print_title(); 
  print qq{<p style="font-size:200%; font-weight: bold; },
        qq{text-align: center; margin-top: 1em; margin-bottom: 1em; ">},
        qq{$text->{page_title_attachment_administration}</p>\n};

  print qq{<p style="font-size:90%; ">};
  print qq{ [ <a href="$cgi_url?op=attachment administration">$text->{dialog_return_to} },
        qq{<b>$text->{page_title_attachment_administration}</b></a> ]\n},br;
  print qq{ [ <a href="$cgi_url">$text->{dialog_return_to} <i>$settings->{'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{$text->{dialog_notice_archive_removed}};
  }
  print br;
  print qq{ [ <a href="$cgi_url?op=attachment administration">$text->{dialog_return_to} },
        qq{<b>$text->{page_title_attachment_administration}</b></a> ]\n},br;
  print qq{ [ <a href="$cgi_url">$text->{dialog_return_to} <i>$settings->{'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=>"$settings->{'title_tag'} - $text->{page_title_attachment_administration}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print qq{<p />};
  print start_form(-action=>$cgi_url, -method=>"post");
  print qq{<p />};
  print_title(); 
  print qq{<p />};
  print qq{<center>};
  print qq{<p style="margin-bottom: 0; font-size:200%; },
        qq{font-weight: bold; margin-top: 1em; ">$text->{page_title_attachment_administration}</p>};
  print qq{<p 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> $text->{dialog_error_archive_action_must_match}},
            qq{ <blink>***</blink></b></font>},br;
      print qq{$text->{dialog_notice_archive_no_action_taken}};
      $action = 'none';
    }
    elsif ($action eq 'delete') 
    {
      print qq{<font color="#AA0000"><b>*** $text->{radio_button_labels_attachment_actions}->{delete} ***</b></font>};
    }
    elsif ($action eq 'archive-iso') 
    {
      print qq{$text->{radio_button_labels_attachment_actions}->{"archive-iso"}};
    }
    elsif ($action eq 'archive-tar') 
    {
      print qq{$text->{radio_button_labels_attachment_actions}->{"archive-tar"}};
    }
    elsif ($action eq 'archive-zip') 
    {
      print qq{$text->{radio_button_labels_attachment_actions}->{"archive-zip"}};
    }
    else 
    {
      print qq{*** $text->{dialog_error_no_archive_action_selected} ***};
      $action = 'none';
    }
    print qq{</p>};
    print qq{</center>\n};

    print qq{<p style="font-size:90%; ">};
    print qq{ [ <a href="$cgi_url">$text->{dialog_link_abort_and_return_to} <i>$settings->{'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>$text->{dialog_archive_summary}:</b>},br;
# set up the attachment management table 
    print qq{<table border=1>};
    print qq{<tr class="fresh_title">};
    print qq{<th>table_column_head_last_access$text->{table_column_head_id}</th>};
    print qq{<th>$text->{table_column_head_filename}</th>};
    print qq{<th>$text->{table_column_head_filesize}</th>};
    print qq{<th>$text->{table_column_head_user}</th>};
    print qq{<th>$text->{table_column_head_folder}</th>};
    print qq{<th>$text->{table_column_head_date_added}</th>};
    print qq{<th>$text->{table_column_head_last_access}</th>};
    print qq{<th>MD5sum</th>};
    print qq{</tr>\n};

    # gather attachment information
    $query = qq{SELECT attachment.id,
                       $settings->{'user_table'}.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 $settings->{'user_table'}.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 class="fresh_body">}; # 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 qq{<b>$action: $text->{dialog_total_size} = $archive_size</b>},br,br;

    print qq{<b>$text->{dialog_textarea_label_comments}:</b>},br;
    print qq{<textarea name="archive_comment" cols="80" rows="15">};
    print qq{$text->{dialog_archive_comments_sample_text}</textarea>},br;

    print qq{<font style="font-weight: bold; color:#AA0000; ">},
          qq{$text->{dialog_warning_delete_are_permanent}</font>},br
          if $action eq 'delete';
    print submit(-name=>'op', -value=>'create archive',
                 -label=>$text->{button_label_create_archive}),br unless $action eq 'none';
    print qq{ [ <a href="$cgi_url">$text->{dialog_link_abort_and_return} <i>$settings->{'title_tag'}</i></a> ]\n};
  }
  else
  {
    print $text->{dialog_notice_archive_volume_in_place};
  }
  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=>"$settings->{'title_tag'} - $text->{page_title_attachment_administration}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                     -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                     -meta=>{'viewport'=>'width=device-width'},
                     -style=>{-src=>"$stylesheet"});
    print qq{<p />};
    print_title(); 
    print qq{<p />};
    print qq{<center>};
    print qq{<p style="margin-bottom: 0; font-size:200%; font-weight: bold; margin-top: 1em; ">},
          qq{$text->{page_title_archive_creation}</p>};
    if ($action ne $confirmation) 
    {
      print qq{<p style="margin-top: 1em; font-size:150%; font-weight: bold; margin-bottom: 0; ">};
      print qq{<font color="#AA0000"><b><blink>***</blink> },
            qq{$text->{dialog_error_archive_action_must_match} <blink>***</blink></b></font>},br;
      print qq{$text->{dialog_notice_archive_no_action_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 style="font-size:90%; ">};
    print qq{ [ <a href="$cgi_url">$text->{dialog_return_to} <i>$settings->{'title_tag'}</i></a> ]},br;
    print qq{ [ <a href="$cgi_url?op=attachment administration">$text->{dialog_return_to} <b>},
          qq{$text->{page_title_attachment_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>$text->{dialog_archive_summary}:</b><br>\n};
    $doc_contents .= qq{<p style="margin-top: 0; font-size:110%; font-weight: bold; margin-bottom: 0; ">};
    my $title_contents = qq{$archive_date - $action - $settings->{'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 class="fresh_title">};
    $doc_contents .= qq{<th>$text->{table_column_head_id}</th>};
    $doc_contents .= qq{<th>$text->{table_column_head_localfile}</th>};
    $doc_contents .= qq{<th>$text->{table_column_head_filename}</th>};
    $doc_contents .= qq{<th>$text->{table_column_head_filesize}</th>};
    $doc_contents .= qq{<th>$text->{table_column_head_user}</th>};
    $doc_contents .= qq{<th>$text->{table_column_head_folder}</th>};
    $doc_contents .= qq{<th>$text->{table_column_head_date_added}</th>};
    $doc_contents .= qq{<th>$text->{table_column_head_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="$settings->{'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,
                       $settings->{'user_table'}.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 $settings->{'user_table'}.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 class="fresh_body">}; # 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>$action: $text->{dialog_total_size} = $archive_size</b><br><br>\n";

    $doc_contents .= qq{<b>$text->{dialog_textarea_label_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 = $dbh->quote($archive_summary);
    $title_contents = $dbh->quote($title_contents);
    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">$text->{dialog_return_to} },
          qq{<b>$text->{page_title_attachment_administration}</b></a> ]\n},br;
    print qq{ [ <a href="$cgi_url">$text->{dialog_return_to} <i>$settings->{'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=>"$settings->{'title_tag'} - $text->{page_title_archive_delete}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print qq{<p />};
  print_title(); 
  print qq{<p />};
  print qq{<center>};
  print qq{<p style="margin-bottom: 0; font-size:200%; },
        qq{font-weight: bold; margin-top: 1em; ">$text->{page_title_archive_delete}</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 qq{$text->{dialog_text_removing} <b>$archive_name[0]</b>};
  printf "  ( %s )....\n", scalar localtime($write_secs);

  print br,br;

  if ($archive_name[0] =~ m/^([\w._\/]+)$/ ) { $archive_name[0] = $1; } # detaint filename
  unlink ("$archive_name[0]") || die "died trying to delete attachment archive";
  print qq{$text->{dialog_text_archive_removed}},br;
  print qq{ [ <a href="$cgi_url?op=attachment%20administration">$text->{dialog_return_to} },
        qq{$text->{page_title_attachment_administration}</a> ]\n},br;
  print qq{ [ <a href="$cgi_url">$text->{dialog_return_to} <i>$settings->{'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=>"$settings->{'title_tag'} - $text->{page_title_search}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});
  print start_form(-action=>$cgi_url, -method=>"post");
  print_folder();
  print_title(); 
  print qq{<center><h1>$text->{page_title_search}</h1></center>};

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

  # 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 = "$text->{search_order_description_A}";
  }
  elsif ( $sort_order eq "B" ) # sort by date/time DESC
  {
    $order = "message.time DESC";
    $order_description = "$text->{search_order_description_B}";
  }
  elsif ( $sort_order eq "C" ) # sort by date/time ASC
  {
    $order = "message.time ASC";
    $order_description = "$text->{search_order_description_C}";
  }
  elsif ( $sort_order eq "D" ) # sort by user name ASC
  {
    $order = "user.name ASC";
    $order_description = "$text->{search_order_description_D}";
  }
  elsif ( $sort_order eq "E" ) # sort by user name DESC
  {
    $order = "user.name DESC";
    $order_description = "$text->{search_order_description_E}";
  }
  elsif ( $sort_order eq "F" ) # sort by folder name ASC
  {
    $order = "folder.name ASC";
    $order_description = "$text->{search_order_description_F}";
  }
  elsif ( $sort_order eq "G" ) # sort by folder name DESC
  {
    $order = "folder.name DESC";
    $order_description = "$text->{search_order_description_G}";
  }
  else
  {
    $order = "score DESC";
    $order_description = "$text->{search_order_description_A}";
  }

  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, $term);
    my $query_counter = 0;
    foreach my $term (@terms)
    {
      $term = $dbh->quote($term);
      $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 ( $full_text_query )
                  AND message.userid = $settings->{'user_table'}.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 ();
    my $pages = ceil($results_count/$results_limit);
    my $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">};
    print qq{$order_description},br;
    print qq{$text->{dialog_change_search_resutls_order} };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=A">}
          unless $sort_order eq "A";
    print qq{$text->{dialog_link_search_results_order_by_relivance}};
    print qq{</a> };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=B">}
          unless $sort_order eq "B";
    print qq{$text->{dialog_link_search_results_order_by_time_desc}};
    print qq{</a> };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=C">}
          unless $sort_order eq "C";
    print qq{$text->{dialog_link_search_results_order_by_time_asc}};
    print qq{</a> };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=D">}
          unless $sort_order eq "D";
    print qq{$text->{dialog_link_search_results_order_by_user_asc}};
    print qq{</a> };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=E">}
          unless $sort_order eq "E";
    print qq{$text->{dialog_link_search_results_order_by_user_desc}};
    print qq{</a> };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=F">} 
          unless $sort_order eq "F";
    print qq{$text->{dialog_link_search_results_order_by_folder_asc}};
    print qq{</a> };
    print qq{<a href="$cgi_url?op=search&searchquery=$searchquery&sort=G">} 
          unless $sort_order eq "G";
    print qq{$text->{dialog_link_search_results_order_by_folder_desc}};
    print qq{</a> };
    print br;
    print qq{[$results_count $text->{dialog_label_search_results} -- $text->{dialog_label_page} },
          qq{$page $text->{dialog_label_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="$text->{dialog_link_title_prev_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="$text->{dialog_link_title_jump_to_page} $i">} if $i != $page; #"
      print qq{<b><font class="fresh_title" style="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="$text->{dialog_link_title_next_page}">&gt;&gt;</a>} #"
            if $page < $pages;
    print qq{</p>};

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

    my $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'),
                      $settings->{'user_table'}.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 = $settings->{'user_table'}.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 qq{$text->{dialog_search_no_results} "$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="$icon->{found_folder}->[0]" width=$icon->{found_folder}->[1] }, #"
              qq{height=$icon->{found_folder}->[2] border=0 alt="[$name]">};
        print qq{<b><a href="$cgi_url?op=expand&message=$message_root&folder=$path&mark=$highlight_query">},
              qq{$name</a></b>, $text->{dialog_by} <b>$message_user</b> $text->{dailog_on} $time},br,"\n";
        }
      else {
        $path = regress_folders($message_folderid);
        print qq{<img src="$icon->{found_message}->[0]" width="$icon->{found_message}->[1]" },
              qq{height="$icon->{found_message}->[2]" border="0" alt="[$name]" />},
              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), $text->{dialog_by} <b>$message_user</b> },
              qq{$text->{dialog_on} $time},br,"\n";
        }
      }
    $sth->finish ();

    $last_page = $page - 1;
    print qq{<p class="navigation">};
    print qq{[$results_count $text->{dialog_label_search_results} -- $text->{dialog_label_page} },
          qq{$page $text->{dialog_label_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="$text->{dialog_link_title_prev_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=$text->{dialog_link_title_jump_to_page}" $i">} if $i != $page;
      print qq{<b><font class="fresh_title" style="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="$text->{dialog_link_title_next_page}">&gt;&gt;</a>} #"
            if $page < $pages;
    print qq{</p>};

    }

  $sth->finish ();
  $dbh->disconnect ();
  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 "Unable to open file: $!\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=>"$settings->{'title_tag'} - $text->{page_title_error_file_not_available}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                       -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                       -meta=>{'viewport'=>'width=device-width'},
                       -style=>{-src=>"$stylesheet"});
      print h1("RealizationEngine $text->{page_title_error_file_not_available}");
      print qq{$text->{dialog_file_not_available}},br,br;
      print h3("$text->{dialog_heading_file_information}");
      print qq{$text->{dialog_file_id}: $local_file_name},br;
      print qq{$text->{dialog_file_name}: $filename},br;
      print qq{$text->{dialog_note_file_offline}};
      }
    }
  else { # else, send appropriate page headers, and inform of error
    print header(), qq{$text->{dialog_error_file_error}},br,br;
    # print qq{$query};
    }
  }
elsif ( $op eq 'change settings' && $name eq 'root' ) {
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$settings->{'title_tag'} - $text->{page_title_change_settings}",
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -style=>{-src=>"$stylesheet"});

  $query = qq{SELECT name, value FROM settings};
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  print start_form(-action=>"$cgi_url", -method=>"post");
  print qq{$text->{dialog_settings}:},br,qq{\n};
  print qq{<table border=1>};
  my ($name, $value);
  while ( @ary = $sth->fetchrow_array () ) {
    ($name, $value) = @ary;
    next if $name eq "page_background" || $name eq "message_background" # skip over the legacy stuff
            || $name eq "fresh_message_background" || $name eq "fresh_title_background"
            || $name eq "warm_message_background" || $name eq "warm_title_background"
            || $name eq "day_old_message_background" || $name eq "day_old_title_background"
            || $name eq "guest_font_color" ;
    $value{$name} = $value;
    print qq{<tr><td align=right>$name: </td>\n};
    if ($name eq "title") {
      my $display_title = $value;
      $display_title =~ s/\$(\w+)/$value{$1}/g;
      print qq{<td><b>$display_title</b></td>},
            qq{<center><font class="navigation"><i>(may not dispaly properly)</i></font></center></td>\n};
      print qq{<td>},textarea(-name=>$name, -value=>$value, -cols=>50, -rows=>20, -wrap=>"off"),
            qq{</td></tr>\n};
      }
    else {
      print qq{<td><b>$value</b></td>\n};
      print qq{<td>},textfield(-name=>$name, -value=>$value, size=>50),qq{</td></tr>\n};
      }
    }
  $sth->finish ();
  $dbh->disconnect ();

  print qq{</table>};
  print submit(-name=>"op", -value=>"update settings", -label=>$text->{button_label_update_settings});
  print end_form();

}
elsif ( $op eq 'update settings' && $name eq 'root' ) {
  print header(-cookie=>[$cookie]);
  print start_html(-title=>"$settings->{'title_tag'} - $text->{page_title_update_settings}",
                   -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                   -meta=>{'viewport'=>'width=device-width'},
                   -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                   -style=>{-src=>"$stylesheet"});

  print qq{<p />};
  my @names = param();
  foreach $name (@names) {
    next if ($name eq 'user' || $name eq 'pass' || $name eq 'database' || $name eq 'op');
    my $value = param($name);
    $query = qq{UPDATE settings SET value=? WHERE name=?};
    my $sth = $dbh->prepare ($query);
    $sth->execute ($value, $name);
    print qq{<b>$name =</b> },escapeHTML($value),br;
    print qq{<table><td valign="top"><b>rendered title:</b> </td><td valign="top">},
          $value,"</td></table>",br if $name eq 'title';
    }

  print hr();
  print qq{<a href="$cgi_url">$text->{dialog_return_to} $settings->{'title_tag'}</a>};
  print end_form();
  print end_html();
  print qq{</p>};
  }

else {
 # $t0 = new Benchmark;
  # my ($count, $childcount, $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=>"$settings->{'title_tag'} - $folder", 
                 -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                 -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                 -meta=>{'viewport'=>'width=device-width'},
                 -expires=> "Mon, 26 Jul 1997 05:00:00 GMT",
                 -cache_control=> "post-check=0,pre-check=0",
                 -pragma=> "no-cache",
                 -style=>{-src=>"$stylesheet"}),"\n";

      # print qq{<font style="font-size: 200%; font-weight: bold; ">$folder</font>},br;
      print start_form(-action=>"$cgi_url", -method=>"post");
      print_title();
      print_folder();
      if( $name ) 
      {
        $text->{welcome_msg} =~ s/\%NAME/$name/g;
        print $text->{welcome_msg}," ";
        print qq{<span class="navigation">};
        print submit(-name=>"op", -value=>"account maintenance", 
                     -label=>$text->{button_label_account_maintenance}, 
                     -title=>$text->{button_title_account_maintenance});
        print qq{&nbsp;}x30; # insert 30 hardspaces
        print submit(-name=>"op", -value=>"logout", -label=>$text->{button_label_logout},
                     -title=>"$text->{button_title_logout}");
        print qq{</span>};
      }
      else 
      {
        print qq{<table class="fresh_title" border=1 cellpadding=5><tr><td>};
        print submit(-name=>"op", -value=>"login", -label=>$text->{button_label_login},
                     -class=>"navigation", -title=>$text->{button_title_login});
        print qq{<span class="navigation">};
        print qq{&nbsp;}x4,qq{ $text->{dialog_new_users}: };
        print qq{</span>};
        print submit(-name=>"op", -value=>"create new account", -label=>$text->{button_label_new_account}, 
                     -class=>"navigation");
        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 class="fresh_body">};
        print qq{[ <a href="$cgi_url?op=user%20administration">$text->{dialog_link_user_admin}</a> ]};
        print qq{[ <a href="$cgi_url?op=group%20administration">$text->{dialog_link_group_admin}</a> ]};
        print qq{[ <a href="$cgi_url?op=change%20settings">$text->{dialog_link_settings_admin}</a> ]};
        print  qq{</td></tr></table>};
      }

      unless ( $op eq 'isolate')
      {
        print qq{<table border=0 class="fresh_body"><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 qq{<input type="hidden" name="folder" value="$folder" />},
                qq{<span style="margin-top:0; margin-bottom:0; ">},
                qq{<img src="$icon->{new_message}->[0]" width=$icon->{new_message}->[1] },
                qq{height=$icon->{new_message}->[2] border=0 />},
                submit(-name=>"op", -value=>"new entry", -label=>$text->{button_label_new_entry}, 
                       -title=>$text->{dialog_link_title_new_entry}, -class=>"navigation",
                       -style=>"padding: 0; "),br,
                "</span>"; # "
        }

        print qq{<img src="$icon->{search}->[0]" width=$icon->{search}->[1] height=$icon->{search}->[2] },
              qq{border=0 />},
              submit(-name=>"op", -value=>"search", -title=>"$text->{button_title_search}", 
                  -label=>"$text->{button_label_search}", -class=>"navigation", -style=>"padding: 0; "),br
              if $settings->{'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 qq{<input type="hidden" name="folder" value="$folder" />};
          print qq{<span style="margin-top:0; margin-bottom:0; ">};
          print qq{<img src="$icon->{new_folder}->[0]" width=$icon->{new_folder}->[1] },
                qq{height=$icon->{new_folder}->[2] border=0 />},
                submit(-name=>"op", -value=>"new folder", -title=>"$text->{button_title_new_folder}", 
                    -label=>"$text->{button_label_new_folder}", -class=>"navigation", -style=>"padding: 0; "),br; #"
          print qq{</span>};
        }

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

#  $t0 = new Benchmark;
        # 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 info on the parent folder
          $query = qq{SELECT parent.id, parent.groupr, parent.otherr, parent.groupid, parent.userid
                         FROM message message, message parent
                         WHERE message.id=$folderid AND parent.id=message.parentid};
          $sth = $dbh->prepare ($query);
          $sth->execute ();

          my ($parent_folderid, $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] = qq{<a href="$cgi_url?folder=$parentfolder&expand=$expand">}.
                                         qq{<img src="$icon->{up}->[0]" width=$icon->{up}->[0] }.
                                         qq{height=$icon->{up}->[0] border=0 />$parentname</a>}; #"

          if ( $parent_group_member eq 'Y' || $parent_otherr eq 'Y' || $name eq 'root' )
          {
            my $children = countchildren($parent_folderid);
            if ($children->{last_posted})
            {
              $folder_array[$folder_count] .= qq{ ($children->{count}/<font color="#3333dd"><b>}.
                                              qq{$children->{today}</b></font>/};
              $folder_array[$folder_count] .= qq{<font color=#EE0000><b>} if $children->{session} > 0;
              $folder_array[$folder_count] .= qq{$children->{session}};
              $folder_array[$folder_count] .= qq{</b></font>} if $children->{session} > 0;
              $folder_array[$folder_count] .= qq{)};
            }
          }
          $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;
          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=>"$text->{dialog_link_title_open_folder}, '$childfolder'"},
                      qq{<img src="$icon->{nav_folder}->[0]" width=$icon->{nav_folder}->[1]},
                      qq{height=$icon->{nav_folder}->[2] border=0 alt="[$childfolder]"/>$childfolder});
                $printed_flag = 'Y'; # yes, we printed the folder
              }
            }
            else 
            {
              unless ($childfolder =~ /^\./ || $childfolder =~ /^~\./)
              {
                $folder_array[$folder_count] .= qq{<img src="$icon->{nav_folder}->[0]"}.
                      qq{ width=$icon->{nav_folder}->[1] height=$icon->{nav_folder}->[2]}.
                      qq{ 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;
                my $children = countchildren($childfolderid);
                my $foldercount = countfolders($childfolderid);
                $folder_session_message_count = quick_folder_count($childfolderid) || 0;
                if ($children->{last_posted})
                {
                  $folder_array[$folder_count] .= 
                            qq{ ($children->{count}/<font color=#3333dd><b>$children->{today}</b></font>/};
                  $folder_array[$folder_count] .= qq{<font color=#EE0000><b>} if $children->{session} > 0;
                  $folder_array[$folder_count] .= qq{$children->{session}};
                  $folder_array[$folder_count] .= qq{</b></font>} if $children->{session} > 0;
                  $folder_array[$folder_count] .= qq{)};
                }
                if ( $foldercount ) 
                {
                  $folder_array[$folder_count] .= qq{ <font color=#009900>($foldercount $text->{dialog_label_folders}};
                  $folder_array[$folder_count] .= " / <font color=\"#AA0000\"><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"},
                      qq{<img src="$icon->{nav_folder_locked}->[0]" width=$icon->{nav_folder_locked}->[1] },
                      qq{height=$icon->{nav_folder_locked}->[2] border=0 alt="$childfolder" />$childfolder}); #"
                $printed_flag = 'Y'; # yes, we printed the folder
              }
            }
            else 
            {
              if ($childfolder =~ /^\w/) 
              {
                $folder_array[$folder_count]
                   .= qq{<img src="$icon->{nav_folder_locked}->[0]" width=$icon->{nav_folder_locked}->[1] }.
                      qq{height=$icon->{nav_folder_locked}->[2] 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;
                my $children = countchildren($childfolderid);
                $foldercount = countfolders($childfolderid);
                if ($last_post_time) 
                {
                  $folder_array[$folder_count] .= qq{ ($children->{count}/<font color="#3333ff"><b>}.
                                                  qq{$children->{today}</b></font>/};
                  $folder_array[$folder_count] .= qq{<font color=#EE0000><b>} if $children->{session} > 0;
                  $folder_array[$folder_count] .= qq{$children->{session}};
                  $folder_array[$folder_count] .= qq{</b></font>} if $children->{session} > 0;
                  $folder_array[$folder_count] .= qq{)};
                }
                if ( $foldercount ) 
                {
                  $folder_array[$folder_count] .= qq{ <font color=#009900>($foldercount folder};
                  $folder_array[$folder_count] .= qq{s} if $foldercount>1;
                  $folder_array[$folder_count] .= qq{)</font>};
                }
              }
            }
# only print <br> if we've printed a folder name
            $folder_array[$folder_count] .= " $text->{dialog_folder_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 ();
        }
# $t1 = new Benchmark;
# $td = timediff($t1, $t0);
# $tf = timestr($td);
# print br,"benchmarks: tf = $tf",br;

        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 class="navigation" 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>};
        
# set up a temporary container for stuff passed to plugins to keep a plugin from
# making potentially dangerous changes
      my $local_userid = $our_userid;
      my $local_username = $username; 
      my $local_groupmember = $groupmember;
      my $local_open = $open;
      my $local_ownerid = $ownerid;
      my $local_groupid = $groupid;
# One hashref for everything we're going to be passing to plugins
      my $folder_navigation_data = { 'dbh' => \$dbh,
                                    'folderid' => \$folderid,
                                    'foldername' => \$foldername,
                                    'username' => \$local_username,
                                    'userid' => \$local_userid,
                                    'ownerid' => \$local_ownerid,
                                    'groupid' => \$local_groupid,
                                    'groupmember' => \$local_groupmember,
                                    'open' => \$local_open,
                                     };
# apply plugins to folder navigation area
        for (my $plugin=0; $plugin<@{$hooks->{'folder_navigation_area'}}; $plugin++) {
          &{$hooks->{'folder_navigation_area'}->[$plugin]}($folder_navigation_data);
        }

          print qq{</td></tr>};
          print qq{<tr><td colspan="3">};
          quick_jump_menu(1);
          print qq{</td></tr>};
          print qq{</table>};

          # Quick stats for root user (only display in root folder
          if ( $folderid == 1 && $name eq 'root' ) 
          {
            # 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 class="fresh_body"><tr class="navigation"><th></th><th>$text->{summary_table_head_messages}</th><th>$text->{summary_table_head_users}</th></tr>};

            # last 24 hours
            print qq{<tr><td>$text->{summary_table_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 qq{<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 qq{<td>$last_24</td>};

          print qq{</tr>};

          # last 7 days
          print qq{<tr><td>$text->{summary_table_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 qq{<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 qq{<td>$last_7</td>};

          # totals
          print qq{<tr><td>$text->{summary_table_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 qq{<td>$total</td>};

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

          # close user sumary table box
          print qq{</tr>};
          print qq{</table>};

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

          # set up file summary table
          print qq{<table class="fresh_body" border=1><tr class="navigation"><th></th><th>$text->{summary_table_files}</th><th>$text->{summary_table_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);
          my $in_place_files = 0;
          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>$text->{summary_table_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>$text->{summary_table_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">$text->{dialog_link_attachment_admin}</a> ]</td></tr>};

          # close file sumary table box
          print qq{</tr>};
          print qq{</table>};

          # close sumary table box
          print qq{</tr>};
          print qq{</table>};

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

          print qq{</span>};
        }
      
        print qq{<table cellpadding="0" cellspacing="0"><tr><td>};
        # Surround the Recently online and folder summary with a table to make it not "get lost."
        print qq{<table align="left" class="fresh_body" style="padding: 0px; margin: 0px 2px 0px },
              qq{0px; border: solid black 1px; " ><tr><td><font class="navigation" style="color: },
              qq{#666666; font-weight: bold; ">};
        print qq{$text->{dialog_notice_recently_online}: $currently_online</font>},br if $name;
        # Printer folder summary
        if ( $groupmember eq 'Y' 
              || $otherr eq 'Y' 
              || $name eq 'root' ) 
        {
          my $children = countchildren($folderid);
          print qq{<font class="navigation">};
          print qq{$children->{count} $text->{dialog_note_total}} if $children->{count};
          print qq{, } if $children->{count} && $children->{today};
          print qq{ $children->{today} $text->{dialog_note_today} \@ $children->{last_posted}} if $children->{today};
          print qq{, } if $children->{week} > $children->{today};
          print qq{$children->{week} $text->{dialog_note_this_week}} if $children->{week} > $children->{today};
          print qq{ <font class=qq{fresh_title}><b> &nbsp; $text->{dialog_note_session_messages}: $children->{session} &nbsp;</b></font>} 
                if $children->{session};
          print qq{</font>};
        }
        print qq{</td></tr></table>};
        # quick_jump_menu(1);
        # print br;
        print qq{</td></tr></table>};
        # 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'},
                       -meta=>{'viewport'=>'width=device-width'},
                       -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                       -expires=> "Mon, 26 Jul 1997 05:00:00 GMT",
                       -cache_control=> "post-check=0,pre-check=0",
                       -pragma=> "no-cache",
                       -style=>{-src=>"$stylesheet"}),"\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);
      # 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 message.id=threads.id AND message.open = 'Y'
                         ORDER BY threads.time $first_level_thread_order};
      my $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
      my $threads = int ( $sth->execute() );
      $pages = ceil($threads/$settings->{'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/$settings->{'thread_limit'});
      }
      $sth->finish ();
      # $url_folder = $linkfolder;

# print links for different thread styles
      unless ( $op eq 'isolate' )
      {
        print qq{<table align=right class="change_thread_style_dialog"><tr><td>};
        print qq{<div class="navigation">},
              qq{$text->{dialog_change_thread_style}: };
        print qq{ <a href="$cgi_url?folder=$url_folder&expand=1&page=$page" },
              qq{title="$text->{dialog_link_title_expand_1}">} unless $expand==1;
        print qq{1</a> };
        print qq{<a href="$cgi_url?folder=$url_folder&expand=2&page=$page" },
              qq{title="$text->{dialog_link_title_expand_2}">} unless $expand==2; #"
        print qq{2</a> };
        print qq{<a href="$cgi_url?folder=$url_folder&expand=3&page=$page" },
              qq{title="$text->{dialog_link_title_expand_3}">} unless $expand==3; #"
        print qq{3</a> };
        print qq{<a href="$cgi_url?folder=$url_folder&expand=4&page=$page" },
              qq{title="$text->{dialog_link_title_expand_4}">} 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='$text->{dialog_link_title_exapand_all}'>}, #'
            qq{$text->{dialog_link_exapand_all}</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='$text->{dialog_link_title_expand_week}'>},
            qq{$text->{dialog_link_expand_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='$text->{dialog_link_title_collapse_old}'>$text->{dialog_link_collapse_old}</a> ]},br #'
                if $op eq 'expand' && $thread_style != 3;
      print qq{<p class="navigation">[ <a href="$cgi_url?folder=$url_folder&},
            qq{page=$page&expand=$thread_style">$text->{dialog_link_collapse_old}</a> ]},br
            if $expand==3 && $thread_style != 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="$text->{dialog_link_title_prev_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="$text->{dialog_link_title_jump_to_page} $i $text->{dialog_label_of} $pages">} 
                if $i != $page; #"
          print qq{<b><font class="current_page_number" },
                qq{style="padding: 0px 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; };
        }
        $next_page = $page + 1;
        print qq{<a href="$cgi_url?folder=$url_folder&page=$next_page&expand=$expand" },
              qq{ title="$text->{dialog_link_title_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="$text->{dialog_link_title_one_page}">},
              qq{$text->{dialog_link_one_page}</a>} if $pages > 1; #"
      }
      else
      {
        print qq{[ <a href="$cgi_url?folder=$url_folder&expand=$expand" },
              qq{ title="$text->{dialog_link_title_return_to}">$text->{dialog_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'},
                         -meta=>{'viewport'=>'width=device-width'},
                         -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                         -expires=> "Mon, 26 Jul 1997 05:00:00 GMT",
                         -cache_control=> "post-check=0,pre-check=0",
                         -pragma=> "no-cache",
                         -style=>{-src=>"$stylesheet"}),"\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="$text->{dialog_link_title_prev_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="$text->{dialog_link_title_jump_to_page} $i $text->{dialog_label_of} $pages">} 
                if $i != $page;
          print qq{<b><font class="fresh_title" style="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="$text->{dialog_link_title_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="$text->{dialog_link_title_one_page}">},
              qq{$text->{dialog_link_one_page}</a>} if $pages > 1;
      }
      else
      {
        print qq{[ <a href="$cgi_url?folder=$url_folder&expand=$expand" },
              qq{ title="$text->{dialog_link_title_return_to}">$text->{dialog_return_to} $folder</a> ]}
      }
      print qq{</p>};
    }

    print br,$settings->{'page_footer'} if $settings->{'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;
# $td = timediff($t1, $t0);
# $tf = timestr($td);
# print br,"benchmarks: tf = $tf",br;
# warn qq{complete folder render ($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 = $settings->{'thread_limit'}*($page-1);

  unless ( defined($limit) || $page == -1 )
  {
    $limit = qq{LIMIT $offset_threads,$settings->{'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, $settings->{'user_table'}.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, $settings->{'user_table'}.name, message.id,
            ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(time) ) as age, attachment.id
          FROM ( message, $settings->{'user_table'} AS 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, $settings->{'user_table'}.name, message.id,
            ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(time) ) as age, attachment.id
          FROM ($settings->{'user_table'} AS user, message) 
          LEFT JOIN attachment ON (message.id=attachment.message_id)
          WHERE message.parentid=$parent
                # AND folderid=$folderid
                AND message.userid=user.id
                AND message.folder='N' 
                AND message.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;
      
# One hashref for everything we're going to be passing to plugins
      my $hooks_message_data = { 'dbh' => \$dbh,
                                  'messagename' => \$messagename,
                                  'datetime' => \$datetime,
                                  'URI' => \$URI,
                                  'message_username' => \$message_username,
                                  'messageid' => \$messageid,
                                  'age' => \$age,
                                  'attachment' => \$attachment,
                                   };
# apply plugins to everything except the message body text
      for (my $plugin=0; $plugin<@{$hooks->{'message_data'}}; $plugin++) {
        &{$hooks->{'message_data'}->[$plugin]}($hooks_message_data);
      }

      #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*)(?>"[^"]*"|'[^']*'|[^'">])*>#$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.

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

      # expand for level 4 expansion
      if ($thread_style == 4 && $messageid != $expandmessage )
      {
        my ($children) = countthread($messageid);
        $children->{session}=0 unless $children->{session};

        my $border;
        if ($children->{session} > 0 || $age < $folder_session_age)
        {
          print qq{\n<ul>\n};
          print qq{<a name="$messageid"></a>\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 qq{<table>\n}; # start message table
#          print qq{<tr>\n} # start message row for fresh or session messages (thread 1-3)
#              if ( $age < $folder_session_age );
          if ( $age < $folder_session_age )
          {
#            print qq{<td class="message_title"};
#            print qq{ id="session_title" } if ($age < $folder_session_age); # session flag
#            print qq{>\n};
            print qq{<div class="message_title" };
            print qq{class="session_title } if ($age < $folder_session_age); # session flag
            if ($age < $settings->{'fresh_message_time'})
            {
              print qq{fresh_title"};
            }
            elsif ($age < $settings->{'warm_message_time'})
            {
              print qq{warm_title"};
            }
            elsif ($age > $settings->{'warm_message_time'})
            {
              print qq{day_old_title"};
            }
            if ($root_message_id != $messageid) 
            {
              print qq{ style="font-size: 80%; "};
            }
            if ($root_message_id != $messageid) 
            {
              print qq{ style="font-size: 80%;" };
            }
            print qq{>};
#            print qq{<div class="session_title">\n} # print session flag if new since last
#                if ($age < $folder_session_age);              # session
            # 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 class="fresh_title">$1<\/font>/gi;
            }
            $messagename =~ s# #\&nbsp;#g;
            print qq{$display_messagename};
#            print qq{</td></tr>\n};
            print qq{</div>\n};
          }
#          print qq{<tr>};
          if ( $age > $folder_session_age && $messageid != $expandmessage)
          {
#            print qq{<td>\n};
            print qq{<div class="};
            if ($age < $settings->{'fresh_message_time'})
            {
              print qq{fresh_title };
            }
            elsif ($age < $settings->{'warm_message_time'})
            {
              print qq{warm_title };
            }
            elsif ($age > $settings->{'warm_message_time'})
            {
              print qq{day_old_title };
            }
            if ($root_message_id != $messageid) 
            {
              print qq{ navigation};
            }
            print qq{">};
            $messagename =~ s# #\&nbsp;#g;
            print qq{<a href="$cgi_url?op=expand&message=$root_message_id&},
                  qq{expand=$expand#$messageid" title="$text->{dialog_link_title_expand_thread}">};
            print qq{<b>$messagename</b></a>&nbsp;};
            print qq{- };
          }
          else 
          {
#            print qq{<td class="message_body"};
#            print qq{ id="session_message" } if ($age < $folder_session_age); # session flag
#            print qq{>};
            print qq{<div class="};
            print qq{session_message } if ($age < $folder_session_age); # session flag
            if ($age < $settings->{'fresh_message_time'})
            {
              print qq{fresh_body };
            }
            elsif ($age < $settings->{'warm_message_time'})
            {
              print qq{warm_body };
            }
            elsif ($age > $settings->{'warm_message_time'})
            {
              print qq{day_old_body };
            }
            if ($root_message_id != $messageid) 
            {
              print qq{navigation};
            }
            print qq{">};
          }
#          print qq{<div class="session_message">\n} if ($age < $folder_session_age); # session flag
          print qq{<font class="navigation"><b>[$text->{dialog_by}&nbsp;};
          print qq{$message_username};
          $datetime =~ s/\ /\&nbsp;/g;
          print qq{]</b>&nbsp;-&nbsp;$text->{dialog_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 class="navigation">};
            # print qq{dura-link} for durable links
            print qq{&nbsp;[<a href="$cgi_url?op=expand&message=$root_message_id#$messageid" },
                  qq{title="$text->{dialog_link_title_duralink}'$mangled_messagename'">$text->{dialog_link_name_duralink}</a>]}; #"
            # print qq{isolate-link} for isolation links
            print qq{&nbsp;<tt>[<a href="$cgi_url?op=isolate&message=$root_message_id" },
                  qq{title="$text->{dialog_link_title_isolate}},
                  qq{'$mangled_messagename'">$text->{dialog_link_isolate}</a>]</tt>} unless $op eq 'isolate';
            print qq{</font>\n},br,"\n";

          my $sql_query = qq{
                SELECT linkname, contents, attachment.filename, userid
                FROM ( message, $settings->{'user_table'} AS 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="$icon->{link_flag}->[0]" width="$icon->{link_flag}->[1]" },
                    qq{height="$icon->{link_flag}->[2]" align="middle" title="$text->{dialog_link_title_follow_link} };
              $linkname ? print qq{$linkname} : print qq{$URI};
              print qq{" border="0" /> };
              $linkname ? print qq{$linkname} : print qq{$URI};
              print qq{</a>},br,qq{\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/\?/%3F/g; # escape any embeded ?'s in filename
              $internal_attachment_name =~ s/\#/%23/g;
            }
            print qq{<a href="$cgi_url/$internal_attachment_name?op=norefresh&download=$attachment" },
                  qq{title="$text->{dialog_link_title_download} '$attachment_name'">},
                  qq{<img src="$icon->{attachment_flag}->[0]" width=$icon->{attachment_flag}->[1] },
                  qq{height=$icon->{attachment_flag}->[2] 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="$icon->{attachment_flag}->[0]" width=$icon->{attachment_flag}->[1] },
                  qq{height=$icon->{attachment_flag}->[2] border=0 },
                  qq{title='$text->{dialog_link_title_attachment} "$attachment_name"' />} if $attachment;
            if ($URI)
            {
              $URI = escapeHTML("$URI");
              print qq{<img src="$icon->{link_flag}->[0]" width="$icon->{link_flag}->[1]" },
                    qq{height="$icon->{link_flag}->[2]" align="middle" title="$text->{dialog_link_title_link} };
                $linkname ? print qq{$linkname} : print qq{$URI};
              print qq{" />};
            }
          }
          else
          {
          print qq{[ <a href="$cgi_url?folder=$url_folder&op=new%20entry&id=$messageid&expand=$expand" },
                qq{title="$text->{dialog_link_title_reply}">$text->{dialog_link_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">$text->{dialog_link_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 < ($settings->{'edit_interval'}) );

          print qq{[ <a href="$cgi_url?folder=$url_folder&op=bury&id=$messageid&root=$root_message_id" },
                qq{title="$text->{dialog_link_title_disapear}">$text->{dialog_link_disapear}</a> ] } if $name eq 'root';
          }
#          print qq{</td></tr></table>\n};
          print qq{</div>\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, $messagename);
          print qq{</ul>};
          print qq{</ul>};
        }
        else
        {
#          print qq{<table border=0 cellpadding=0 cellspacing=5>};
#          print qq{<tr>};
#          print qq{<td class="message_title"};
#          print qq{ id="session_title" } if ($age < $folder_session_age); # session flag
#          print qq{>};
          print qq{<div };
          print qq{class="message_title level4_title };
          if ($age < $settings->{'fresh_message_time'})
          {
            print qq{fresh_title };
          }
          elsif ($age < $settings->{'warm_message_time'})
          {
            print qq{warm_title };
          }
          elsif ($age > $settings->{'warm_message_time'})
          {
            print qq{day_old_title };
          }
          print qq{session_title} if ($age < $folder_session_age); # session flag
          print qq{">};
          print qq{<a href="$cgi_url?op=expand&message=$root_message_id&},
                qq{expand=$expand#$root_message_id" title="$text->{dialog_link_title_expand_thread}">},
                qq{<b>$messagename</b></a> };
          print qq{<font class="navigation"><b>};
          print qq{[$text->{dialog_by} $message_username]};
          print qq{</b> - $text->{dialog_posted}: $datetime};
          
          if ($children->{total} > 0)
          {
            print qq{ ($text->{dialog_replies}: $children->{total}/<font color="#3333dd"><b>$children->{today}</b>},
                  qq{</font>/};
            print qq{<font color="#EE0000"><b>} if $children->{session} > 0;
            print qq{$children->{session}};
            print qq{</b></font>} if $children->{session} > 0;
            print qq{)</font>};
          }
          
          print qq{</font></div>};
#          print qq{</td></tr></table>};
          # print qq{</ul>};
        }
        print "\n";
      }
      # message threading for level 1-3
      else
      {
        print qq{<a name="$messageid"></a>\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 qq{<table border=$border cellspacing=1 cellpadding=0>\n}; # start message table
#        print qq{<tr>} # start message row for fresh or session messages (thread 1-3)
#            if ( ( ($age < $settings->{'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 < $settings->{'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 qq{<td  class="message_title"};
#            print qq{ id="session_title"} if ($age < $folder_session_age); # session flag
#            print qq{>\n};
            print qq{<div id="title$messageid" class="};
            print qq{session_title } if ($age < $folder_session_age); # session flag
            if ($age < $settings->{'fresh_message_time'})
            {
              print qq{fresh_title"};
            }
            elsif ($age < $settings->{'warm_message_time'})
            {
              print qq{warm_title"};
            }
            elsif ($age > $settings->{'warm_message_time'})
            {
              print qq{day_old_title"};
            }
            if ($root_message_id != $messageid) 
            {
              print qq{ style="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 class="fresh_title">$1<\/font>/gi;
            }
#            print qq{<div class="session_title">\n} # print session flag if new since last
#                if ($age < $folder_session_age);              # session
            print qq{$display_messagename</div>};
#            print qq{</td></tr>\n};
          }
#        print qq{<tr>\n};
        if ( ( ($age > $settings->{'fresh_message_time'} && $thread_style == 1)
               || ($age > 7*86400 && $thread_style == 5)
                                           || $thread_style == 2)
                  && $age > $folder_session_age
                  && $messageid != $expandmessage) 
        {
#          print qq{<td class="message_title"};
#          print qq{ id="session_title"} if ($age < $folder_session_age); # session flag
#          print qq{>\n};
          print qq{<div id="title$messageid" class="};
          print qq{session_title } if ($age < $folder_session_age); # session flag
          if ($age < $settings->{'fresh_message_time'})
          {
            print qq{fresh_title"};
          }
          elsif ($age < $settings->{'warm_message_time'})
          {
            print qq{warm_title"};
          }
          elsif ($age > $settings->{'warm_message_time'})
          {
            print qq{day_old_title"};
          }
          if ($root_message_id != $messageid) 
          {
            print qq{ style="font-size: 80%; "};
          }
          print qq{>};
          print qq{<a href="$cgi_url?op=expand&message=$root_message_id&},
                qq{expand=$expand#$messageid" title="$text->{dialog_link_title_expand_thread}">};
          print qq{$messagename</a>&nbsp;- };
        }
        else 
        {
#          print qq{<td class="message_body"};
#          print qq{ id="session_message" } if ($age < $folder_session_age); # session flag
#          print qq{>\n};
          print qq{<table><tr><td>};
          print qq{<div id="message$messageid" class="};
          print qq{session_message } if ($age < $folder_session_age); # session flag
          if ($age < $settings->{'fresh_message_time'})
          {
            print qq{fresh_body"};
          }
          elsif ($age < $settings->{'warm_message_time'})
          {
            print qq{warm_body"};
          }
          elsif ($age > $settings->{'warm_message_time'})
          {
            print qq{day_old_body"};
          }
          print qq{>};
        }
      print qq{<font class="navigation"><b>$text->{dialog_by}&nbsp;};
      print qq{$message_username};
      $datetime =~ s/\ /\&nbsp;/g;
      print qq{</b>&nbsp;[&nbsp;$text->{dialog_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 < $settings->{'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 qq{dura-link} for durable links
          print qq{<font class="navigation">};
          print qq{ [<a href="$cgi_url?op=expand&message=$root_message_id#$messageid" },
                qq{title="$text->{dialog_link_title_duralink}'$mangled_messagename'">dura-link</a>]};
          print qq{</font>};
          # print qq{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="$text->{dialog_link_title_isolate}},
                qq{'$mangled_messagename'">$text->{dialog_link_isolate}</a>]</tt>} unless $op eq 'isolate';
          print qq{</font>};
          print br,"\n";

          my $sql_query = qq{
                SELECT linkname, contents, attachment.filename, userid
                FROM ( message, $settings->{'user_table'} AS 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="$icon->{link_flag}->[0]" width="$icon->{link_flag}->[1]" },
                  qq{height="$icon->{link_flag}->[2]" align="middle" title="$text->{dialog_link_title_follow_link}: };
            $linkname ? print qq{$linkname} : print qq{$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 qq{$linkname} : print qq{$URI};
            print qq{</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/\?/%3F/g; # escape any embeded ?'s in filename
            $internal_attachment_name =~ s/\#/%23/g;
          }
          print qq{<a href="$cgi_url/$internal_attachment_name?op=norefresh&download=$attachment" },
                qq{title="$text->{dialog_link_title_download} '$attachment_name'"><img src="$icon->{attachment_flag}->[0]" },
                qq{width=$icon->{attachment_flag}->[1] height=$icon->{attachment_flag}->[2] },
                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="$icon->{attachment_flag}->[0]" width=$icon->{attachment_flag}->[1] },
                  qq{height=$icon->{attachment_flag}->[2] border=0 title='$text->{dialog_link_title_attachment}' />};
          }
          if ($URI)
          {
            $URI = escapeHTML("$URI");
            print qq{<img src="$icon->{link_flag}->[0]" width="$icon->{link_flag}->[1]" },
                  qq{height="$icon->{link_flag}->[2]" align="middle" title="$text->{dialog_link_title_link} };
            $linkname ? print qq{$linkname} : print qq{$URI};
            print qq{" />};
          }
        }
        else
        {
          print qq{[ <a href="$cgi_url?folder=$url_folder&op=new%20entry&},
                qq{id=$messageid&expand=$expand" },
                qq{title="$text->{dialog_link_title_reply}">$text->{dialog_link_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">$text->{dialog_link_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 < ($settings->{'edit_interval'}) );

          print qq{[ <a href="$cgi_url?folder=$url_folder&op=bury&},
                qq{id=$messageid&root=$root_message_id" },
                qq{title="$text->{dialog_link_title_disapear}"},
                qq{>$text->{dialog_link_disapear}</a> ] }
                    if $name eq 'root';
        }
        print qq{</div>\n};
        print qq{</td></tr></table>};
#        print qq{</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);
        }
        print qq{</div>} if $parent == $folderid;
      } # end thread_style 1 # 3 progression
      print qq{</ul>} unless ($thread_style == $expand && $expandmessage!=$messageid && $expand==4);
      }
    $sth->finish ();
    }
  $sth->finish ();
}

sub countchildren 
{
  my ($parent) = @_;
  my $children = { today => 0, week => 0, session => 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();
  ($children->{count}) = $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();
  ($children->{today}) = $sth->fetchrow_array ();
  $sth->finish ();
  $sql_query = qq{ # children for last week (7 days)
                  SELECT count(*)
                  FROM message
                  WHERE time > date_format(date_sub(now(), interval 7 day), '%Y%m%d%H%i%s')
                  AND folderid=$parent AND folder="N" AND open="Y"
                  };
  $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
  $sth->execute();
  ($children->{week}) = $sth->fetchrow_array ();
  $sth->finish ();

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

  $folder_session_age = folder_session_age($parent);
  ($children->{session}) = 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();
  ($children->{last_posted}) = $sth->fetchrow_array ();
  $sth->finish ();
  return ($children);
}

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

    $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
                    };
    $sth = $dbh -> prepare ($sql_query) || die $dbh->errstr;
    $sth->execute();
    ($sessions_children) = $sth->fetchrow_array () || 0;

  return ($sessions_children);
}

sub countthread 
{
  my ($parent) = shift @_;
  my $count = { total => 0, today => 0, week => 0, session => 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;
      $count->{today} ++ if $age < 1*24*3600;
      $count->{week} ++ if ($age < 7*24*3600);
      $count->{session} ++ if $age < $folder_session_age;
      $count->{total} ++;
      my $next_level = countthread($messageid);
      $count->{total} += $next_level->{total} || 0;
      $count->{today} += $next_level->{today} || 0;
      $count->{session} += $next_level->{session} || 0;
      $count->{week} += $next_level->{week} || 0;
    }
  }
  return ($count); # ->{total}, $count->{today}, $count->{week}, $count->{session});
}

sub countfolders 
{
  my($parent) = @_;
  my $foldercount;
  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 $settings->{'user_table'} 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="$icon->{in_root}->[0]" width=$icon->{in_root}->[1] height=$icon->{in_root}->[2] alt="" border=0}; #"
    print qq{ align="top">};
    }
    else
    {
    print qq{<img src="$icon->{in_open_folder}->[0]" width=$icon->{in_open_folder}->[1] height=$icon->{in_open_folder}->[2] alt="" border=0}; # "
    print qq{ align="top">};
    }
  }
  print qq{<img src="$icon->{'in_locked_folder'}->[0]" width=$icon->{'in_locked_folder'}->[1] height=$icon->{'in_locked_folder'}->[2] alt="" border=0\
           align="top">}
        if $open eq 'N'; # "
  print qq{</a>};
  # print qq{ <b>$folder</b>};
  print qq{ <b>$breadcrumb_link</b>};
  print qq{ $text->{dialog_folder_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="$text->{dialog_link_title_attributes}">},
        qq{<img src="$icon->{folder_attributes}->[0]" width=$icon->{folder_attributes}->[1] },
        qq{height=$icon->{folder_attributes}->[2] 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="$text->{dialog_title_folder_group} '$folder_groupname'">};
  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 $settings->{'title'};
}

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

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

  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 $settings->{'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  entries
          || $op eq 'preview'         # ... or previews of  entries
          || $op eq ' 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=>"$settings->{'cookie_name'}") || 'NULL';
  ($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 $settings->{'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 $settings->{'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 = $settings->{'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=>"$settings->{'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 $settings->{'user_table'} SET timestamp=now() WHERE id=$our_userid};
      $query = qq{UPDATE $settings->{'user_table'} 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 $settings->{'recent_interval'} minute) < timestamp
                      AND ours.userid = $our_userid
                      AND thiers.groupid = ours.groupid
                      AND $settings->{'user_table'}.id = thiers.userid
                      GROUP BY $settings->{'user_table'}.id
                };
    $query = qq{SELECT name
                FROM $settings->{'user_table'} 
                WHERE date_sub(now(), interval $settings->{'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 $settings->{'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 $text->{dialog_warning_unconfirmed_account};
        $sth->finish ();
        $dbh->disconnect ();
        exit(0);
      }

      if ($pass_md5 eq md5_hex('deactivate') ) 
      {
        print header();
        print $text->{dialog_warning_account_deactivated};
        $sth->finish ();
        $dbh->disconnect ();
        exit(0);
      }

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

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

      my $passkey = passkey();

      $cookie_domain = $settings->{'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=>"$settings->{'cookie_name'}",
                         -value=>"$passkey-$name",
                          -path=>'/',
                         -domain=>"$cookie_domain",
                         -expires=>'+15d');
      print redirect(-cookie=>[$cookie],
                     -url=>"$cgi_url?folder=$folder");
    }
    else 
    {
      print header();
      print start_html(-title=>"$settings->{'title_tag'} - login",
                       -head=>[Link({-rel=>"SHORTCUT ICON", -href=>'/favicon.ico'})],
                       -meta=>{'MSSmartTagsPreventParsing'=>'True'},
                       -meta=>{'viewport'=>'width=device-width'},
                       -style=>{-src=>"$stylesheet"});
      print qq{<table width=400 border=0 cellpadding=6 cellspacing=3><tr><td class="fresh_body">};
      print start_form(-action=>"$cgi_url", -method=>"post", -name=>"login");
      print qq{<b>$text->{dialog_login}</b>},br;
      print qq{<table border=0>};
      print qq{<tr><td align=right>$text->{dialog_label_username}: </td><td>},textfield(-name=>"username"),
            qq{</td></tr>};
      print qq{<tr><td align=right>$text->{dialog_password}: </td><td>},password_field(-name=>"password"),
            qq{</td></tr>};
      print qq{</table>};
      print qq{<script type="text/javascript"> document.login.username.focus(); </script> };
      print qq{<input type="hidden" name="folder" value="$folder" />};
      print submit(-name=>"op", -value=>"login"),br;
      print qq{</td></tr><tr><td class="fresh_body">};
      print $text->{dialog_notice_no_account},br;
      print submit(-name=>"op", -value=>"create new account", -label=>"$text->{dialog_button_label_new_account}"),br;
      print qq{</td></tr><tr><td class="fresh_body" style="border: solid red; border-width: 1px; ">};
      print qq{<font style="font-weight: bold; ">$text->{dialog_forgot_password}</font>},br;
      print $text->{dialog_forgot_password2},br;
      print qq{<font style="color: red; font-size: 80%; font-weight: bold; "><i>$text->{dialog_warning_password_reset}</i></font>},br;
      print qq{$text->{dialog_email_address}: },textfield(-name=>"email"),br;
      print submit(-name=>"op", -value=>"mail password", -label=>"$text->{button_label_mail_password}"),br;
      print qq{<font style="color: red; font-size: 80%; font-weight: bold; "><i>$text->{dialog_warning_password_reset2}</i></font>},br;
      end_form();
      print qq{</td></tr><tr><td class="fresh_body">};
      print qq{<a href=$cgi_url?>$text->{dialog_loggin_cancel} $settings->{'title_tag'}</a>},br;
      print qq{</td></tr></table>};
      end_html();
      exit(0);
    }
  }
  elsif ($op eq 'logout' && $name) 
  {
    $cookie_domain = $settings->{'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=>"$settings->{'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" class="navigation">};

    print qq{<option value="$root_folder_name">$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 qq{ ($new_messages)} if ($new_messages > 0);

    # Print the "you are here arrow" in the SELECT menu
    print qq{ &lt;---- $text->{quick_jump_here_mark} } 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="selected" } if ($childfolderid == $folderid);
    print qq{/>};
    print $leader x $leaders.qq{|--};
    print qq{&gt;} if ($childfolderid == $folderid);
    print qq{ $childfolder};

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

    # Print the "you are here arrow" in the SELECT menu
    print qq{ &lt;---- $text->{quick_jump_here_mark} } 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" class="navigation" />};
    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 $settings->{'user_table'}.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);
  }

# count the number of session messages in sub folders
  $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 qq{$text->{dialog_move_to}: <select name=new_parent>\n};

    print qq{<option value="1"};
    print qq{ selected="selected" } if ($parentid == 1);
    print qq{ />$root_folder_name};

    # Print the "you are here arrow" in the SELECT menu
    print qq{ &lt;---- "$foldername" $text->{folder_move_is_here_mark} } 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="selected" } if ($childfolderid == $parentid);
    print qq{ />};
    print $leader x $leaders."|--";
    print qq{&gt;} if ($childfolderid == $parentid);
    print qq{ $childfolder (group: $group_name)};

    # Print the "you are here arrow" in the SELECT menu
    print qq{ &lt;---- "$foldername" $text->{folder_move_is_here_mark} } 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
{
# A reference to the message contents is passed rather than the verbose contents
  my ($contents) = shift;
  $$contents =~ s#http://">##sg; # get rid of dangerous HTML

# apply plugins to message body text
  for (my $plugin=0; $plugin<@{$hooks->{'message_body'}}; $plugin++) {
    &{$hooks->{'message_body'}->[$plugin]}($contents);
  }

  # 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/s;
    $test_tag =~ s/([\[\]])/\\$1/g;
    $test_tag =~ s#</?\s*(\w+)(?>\s|"[^"]*"|'[^']*'|[^'">])*>#$1#s;
    TAG_TEST: foreach $approved_tag (@approved_tags)
    {
      next EACH_TAG if $approved_tag eq $test_tag; # tag good, next
    }
    # $line =~ s#$tag##g; # remove the tag if not in approved list
# if tag is not approved, convert <,> to &lt;, &gt; and replace
#    my $failed_tag = $tag;
#    $failed_tag =~ s/</\&lt;/sg; 
#    $failed_tag =~ s/>/\&gt;/sg;
    $$contents =~ s#<(/?)$test_tag#\&lt;$1$test_tag#sg;
    $$contents =~ s#($test_tag(?>"[^"]*"|'[^']*'|[^'">])*)>#$1\&gt;#sg;
  }

# convert apparent URLs to linked URLs
# but not if they are already HTML coded as links
  $$contents =~ s#(?<!= ['"])(?<!=['"])(?<!=)(?<!>)( ?)(https?:\/\/[^() \[\]\t"<>\\\^'{}\|\n]+(\w|\/))#$1<a href="$2">$2<\/a>#ig;
# trim excessively long URLs for length
  $$contents =~ s#>(http\S{40})\S{4,}(\S{7})</a>#>$1....$2</a>#ig;
# convert apparent email addressed to linked email addresses
# but not already linked emails
  $$contents =~ s#(?<!href=mailto:)(?<!href=["']mailto:)(\b\w[-.\w]*\@\w[\w\-\.]+\w\b)#<a href="mailto:$1">$1</a>#g;
# 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; # left-double quote
  $$contents =~ s#\x94#\&rdquo;#g; # right-double quote
  $$contents =~ s#\x96#\&ndash;#g; # n-dash
  $$contents =~ s#\x97#\&mdash;#g; # m-dash

  # highlight search terms in message to make them easy finding
  foreach $mark(@marks)
  {
    $$contents =~ s/\b($mark)\b(?![^<]*?>)/<font class="fresh_title">$1<\/font>/gi;
  }

  my @contents = split (/\r?\n/, $$contents);
  if ($#contents <= 1)
  {
    @contents = split (/\r/, $$contents); 
  }
  my $tag_count;

  foreach my $line (@contents)
  {
# test for start of table, list, or blockquote
# if start of table increment "in_table" flag to supress space translation
    if ($line =~ /<table/i)
    { 
      $_ = $line;
      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 ($line =~ /<(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';
      $line =~ s#  #\&nbsp; #g;
      # odd-man catcher
      $line =~ s#  #\&nbsp; #g;
      # convert leading spaces to &nbsp;
      $line =~ s#^ #\&nbsp;#;
    }

# test for end of table, list, or blockquote
# if end of table, decrement "in_table" flag to reactivate space translation
    if ($line =~ /<\/table/i)
    { 
      $_ = $line;
      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 )
    {
      $line =~ 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 ($line =~ /<\/(ol|ul|blockquote)/i)
      { $in_block--; }
    print $line;
    print br unless ( $line =~ m#</?(ol|ul|li|blockquote|table|th|tr|td|br|/p|dt|dd)>#i );
    print qq{\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 class='fresh_body'>\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) = @_;
  # $user_name = '!'.$user_name;
  my $banged_user_name = $dbh->quote('!'.$user_name);
  $query = qq{ SELECT $settings->{'user_table'}.passkey, $settings->{'user_table'}.email
                FROM user
                WHERE $settings->{'user_table'}.name=$banged_user_name
                };
  $sth = $dbh->prepare ($query);
  $sth->execute ();
  @ary = $sth->fetchrow_array ();
  my ($passkey, $email) = @ary;

  my $temp_domain_name = $settings->{'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 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.

$text->{notification_dialog_do_not_reply}
END_OF_MESSAGE

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

sub human_readable
{
  my ($size) = @_;
  $size = 0 unless $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&nbsp;");
  $size = "$size $units&nbsp;" if ( $units eq "b&nbsp;");
  $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");