automail.pl

#!/usr/local/bin/perl
use warnings; use strict;
our $debug = 0;
our $writeml = 0;
#######################################################
#
#	eTicket, Open Source Support Ticket System
#	http://eticket.sourceforge.net/
#
#	Released under the GNU General Public License
#
#######################################################
#
#	Email Gateway for piping/aliases (automail.pl)
#
#           - DO NOT EDIT THIS FILE -
#######################################################
#

#set the output
binmode STDOUT, ":encoding(UTF-8)";

# Get dir
use FindBin qw($Bin); if ($Bin) { chdir($Bin); }

# define variables for settings.pl
our $version;
our $installed;
our $db_type;
our $db_host;
our $db_name;
our $db_user;
our $db_pass;
our $db_table_prefix;
our $db_table_tickets;
our $rootpath_dir;

# define other variables
our $flood_msg_rate;
our $ANTISPAM_MAGICWORD;
our $ANTISPAM_SUBJECT;
our $ANTISPAM_MSG;
our $ANTISPAM_EMAIL;
our $spamassassin_enable;
our $spamword;
our $no_subject;
our $subject_re;
our $mailcharset;
our $presig;
our $ticket_format;
our $sendmail;
our $myconfig;

our $dbh;
our $eml;
our @message;
our @headers;
our %header;
our $eml_subj;
our $email_from;
our $name_from;
our $body;
our $header;
our $sent_to;
our $sent_from;
our $sent_cc;
our $ticket;
our $remove_tag;
our $signature;
our $category;

# Import settings
require 'settings.pl';
#
# Note: Settings were moved to the above file
#
#######################################################

# Some language messages for errors - haven't decided where to put these yet
my $lang_error = 'Error';
my $lang_error_filext = 'invalid file extention';
my $lang_error_size = 'file is too big';
my $lang_no_msg = "No message was supplied.\n";
my $lang_msg_toobig = "The message is too large.\n";
my $lang_no_to_param = "To: parameter was not found...\n";
my $lang_no_from_param = "No where to reply to...\n";
my $lang_module_err =
  "%module perl module failed to load,\n".
  "Please forward this message to the server admin...\n\n".
  "You can install this perl module using:\n".
  "perl -MCPAN -e'install %module'\n\n";

#check we're installed
if (!$installed) { exit; }

## Innitiate ##

## Load Modules ##
#Note: To install a module do: perl -MCPAN -e'install $module'
my @modules = (
	'DBI',
	'POSIX',
	'Encode',
	'File::Basename',
	'MIME::Base64',
	'MIME::Words qw(:all)',
	'MIME::QuotedPrint',
	'HTML::Entities'
);
if ($spamassassin_enable == 1) { push @modules, 'Mail::SpamAssassin'; }
foreach my $module (@modules) {
	eval("use $module;");
	if ($@) {
	  $lang_module_err =~ s/%module/$module/ig;
		print $lang_module_err;
		exit;
	}
}

#database connection
#use DBI;
$dbh = DBI->connect("DBI:$db_type:$db_name:$db_host", $db_user, $db_pass);
my $sth;

#Disable strict mode in MySQL 5... Bug #1793730
$sth = $dbh->prepare('SET @@session.sql_mode="MYSQL40"'); $sth->execute();
#Fix for "Got a packet bigger than 'max_allowed_packet' bytes" error
### $sth = $dbh->prepare('SET @@session.max_allowed_packet="16M";'); $sth->execute();

#database configuration
$sth = $dbh->prepare('SELECT * FROM '.$db_table_prefix.'config');
$sth->execute();
# $myconfig needs to be global
$myconfig = $sth->fetchrow_hashref;

$sth = $dbh->prepare("SHOW TABLES LIKE '".$db_table_prefix."settings'");
$sth->execute();
my $row=$sth->fetchrow_arrayref;
undef $mailcharset;
if ($row) {
	$sth = $dbh->prepare("SELECT * FROM ".$db_table_prefix."settings");
	$sth->execute();
	while (my $row_hash=$sth->fetchrow_hashref) {
		if ($row_hash->{'key'} eq 'charset') { $mailcharset=$row_hash->{'value'}; }
		if ($row_hash->{'key'} eq 'presig') { $presig=$row_hash->{'value'}; }
		if ($row_hash->{'key'} eq 'ticket_format') { $ticket_format=$row_hash->{'value'}; }
		if ($row_hash->{'key'} eq 'subject_re') { $subject_re=$row_hash->{'value'}; }
		if ($row_hash->{'key'} eq 'spamword') { $spamword=$row_hash->{'value'}; }
		if ($row_hash->{'key'} eq 'flood_msg_rate') { $flood_msg_rate=$row_hash->{'value'}; }
		if ($row_hash->{'key'} eq 'ANTISPAM_MAGICWORD') { $ANTISPAM_MAGICWORD=$row_hash->{'value'}; }
		if ($row_hash->{'key'} eq 'ANTISPAM_SUBJECT') { $ANTISPAM_SUBJECT=$row_hash->{'value'}; }
		if ($row_hash->{'key'} eq 'ANTISPAM_MSG') { $ANTISPAM_MSG=$row_hash->{'value'}; }
		if ($row_hash->{'key'} eq 'ANTISPAM_EMAIL') { $ANTISPAM_EMAIL=$row_hash->{'value'}; }
	}
}

## Functions ##

sub is_valid {
	my $ticket = $_[0];
	if ($ticket) {
		my $sth = $dbh->prepare("SELECT * FROM ".$db_table_tickets." WHERE ID=".$ticket);
		$sth->execute();
		my $hash = $sth->fetchrow_hashref;
		$sth->finish();
		return $hash->{'ID'};
	}
}

# Ticket Generation sub routine
sub get_ticket {
	my $min = $_[0];
	my $max = $_[1];
	my $id;
    do {
        #$ticket = int(rand(999999-100000+1))+100000;
		$id=int(rand(($max-$min)))+$min;
    } while (is_valid($id));

	return $id;
}

sub sendmail {
  my ($to, $from, $subj, $msgbody) = @_;
  if (substr($to,0,1) eq ':') { return; }
  my $eol="\n";
  $to=encode_mimewords($to,('Charset'=>$mailcharset));
  $from=encode_mimewords($from,('Charset'=>$mailcharset));
  $subj=encode_mimewords($subj,('Charset'=>$mailcharset));
  binmode STDOUT, ":encoding(UTF-8)";
  open(MAIL, "|$sendmail -t -f '$from'");
  print MAIL "To: $to".$eol;
  print MAIL "From: $from".$eol;
  print MAIL "Subject: $subj".$eol;
  print MAIL "Precedence: bulk".$eol;
  print MAIL "Content-Type: text/plain; Charset=$mailcharset".$eol;
  print MAIL "X-Mailer: ".$version.$eol;
  print MAIL $eol; #end of headers
  print MAIL $msgbody.$eol;
  close(MAIL);
  return 1;
}

sub get_sendmail {
	my $sendmail = "";
	for (qw(/usr/lib/sendmail /usr/sbin/sendmail /usr/ucblib/sendmail)) {
	    $sendmail = $_, last if -e $_;
	}
	if ($^O eq 'os2' and $sendmail eq "") {
	    my $path = $ENV{PATH};
	    $path =~ s:\\:/: ;
	    my @path = split(';', $path);
	    for (@path) {
		$sendmail = "$_/sendmail", last if -e "$_/sendmail";
		$sendmail = "$_/sendmail.exe", last if -e "$_/sendmail.exe";
		}
	}
	return $sendmail;
}

sub keyword {
	my $input = shift;
	#below are vars for %vars
	my %vars;
	$vars{'ticket'} = $ticket;
  $vars{'subject'} = $eml_subj;
	$vars{'email'} = $email_from;
	$vars{'name'} = $name_from;
	$vars{'message'} = $body;
  $vars{'datetime'} = trim($header{'date'});
	$vars{'local_email'} = $sent_to;
	$vars{'user_email'} = $sent_from;
  $vars{'url'} = $myconfig->{'root_url'};
  $vars{'ticket_max'} = $myconfig->{'ticket_max'};
  $input =~ s/%([_|a-zA-Z][a-zA-Z|\d|_]*)/defined($vars{"$1"})?$vars{"$1"}:"%$1"/egi;
  return $input;
}

sub trim($)
{
	my $string = shift;
	if ($string) {
		$string =~ s/^\s+//;
		$string =~ s/\s+$//;
		return $string;
	}
}

sub decode_mimestring {
	my $str = $_[0];
	my $charset_to = 'UTF-8';
	if ($mailcharset) { $charset_to = $mailcharset; }
	if ($_[1]) { $charset_to = $_[1]; }
	my @data;
	@data = decode_mimewords($str);
	my $output;
	foreach my $arr (@data) {
		my @array = $arr;
		my $out = $array[0][0];
		if ($array[0][1]) {
			$out = convert_charset($out,$array[0][1],$charset_to);
		}
		$output .= $out;
	}
	return $output;
}

#Usage: $body=convert_charset($body,$charset_from,$charset_to=false);
sub convert_charset {
	my $out = $_[0];
	my $charset_from = $_[1];
	my $charset_to = 'UTF-8';
	if ($mailcharset) { $charset_to = $mailcharset; }
	if ($_[2]) { $charset_to = $_[2]; }

	$charset_from =~ s/\"//g;
	$charset_from = uc($charset_from);
	if ($charset_from && $charset_from ne $charset_to) {
    my $unicode_str = Encode::decode($charset_from, $out);
    $out = Encode::encode($charset_to, $unicode_str);
	}
	return $out;
}

#returns the headers in an array
sub get_headers {
	my @headers=();
	foreach (@_) {
		push @headers, $_;
		last if (/^\s$/ || /^$/);
		$_ =~ s/:\s/:/g;
	}
	return @headers;
}

#returns the $header vars
sub set_headers {
	my $previous_header;
	my %header=();
	my @vars;
	foreach (@_) {
   if (/^[^\s:]+:/) {
	  @vars = split(':', $_, 2);
	  if ($vars[1] =~ /.+/) {
	    chomp($header{trim(lc($vars[0]))} = trim($vars[1]));
	    chomp($previous_header=trim(lc($vars[0])));
	  }
   }
   elsif ($previous_header) {
	    $header{$previous_header} = $header{$previous_header}."\n".trim($_);
	 }
	}
	return %header;
}

#get bodies based on boundaries
sub get_bodies {
	my @bodies;
	my @mbodies;
	my $boundary;
	foreach (@_) {
	  if ($_ =~ m/boundary=([^;\s]+)/i) {
	  	$boundary = $1;
	    $boundary =~ s/\"//g;
	    $boundary = '--'.$boundary;
	    @mbodies = split($boundary, $_);
	  	foreach (@mbodies) {
	  		push @bodies, $_;
	  	}
	  }
	  else { push @bodies, $_; }
	}
	return @bodies;
}

#return name,email of an email address
sub get_email {
	foreach (@_) {
	my @out;
	
	if (!$_) { return; }

	$_ =~ s/\'//g;
	$_ =~ s/\"//g;
	
	if (!$_) { return; }

	if ($_ =~ /(.*)<(.+)>/i) {
		$out[0] = trim($1);
		$out[1] = trim(lc($2));
		if (!$out[0]) { $out[0]=$out[1]; }
	}
	else {
		$out[0]=trim(lc($_));
		$out[1]=trim(lc($_));
	}
	##returns: @out(name,email);
	return @out;
	}
}

#This little function writes files for us
sub write_file {
	my $file = shift;
	my $code = shift;
	if (!-e $file) {
		#open(ATTACH, ">$file");
		open my $fh, ">", $file or die "Open $file failed: $!";
		if ($fh) {
			binmode($fh);
			print $fh $code;
			close($fh);
			chmod(0644,$file);
		}
	}
}

#get_cat_reps
sub get_cat_reps {
	my @cat_reps=();
	my @cat_access;
	my @reps = ();
	my $sth;
	my $include;
	foreach (@_) {
	my $catid=$_;
	$sth = $dbh->prepare("SELECT * FROM ".$db_table_prefix."reps");
	$sth->execute();
	while (my $row_reps = $sth->fetchrow_hashref) {
		push @reps, $row_reps;
	}
	foreach my $rep (@reps) {
		$include=0;
		$sth = $dbh->prepare("SELECT cat_access FROM ".$db_table_prefix."groups WHERE ID=".$rep->{'user_group'});
		$sth->execute();
		while (my $group = $sth->fetchrow_hashref) {
			@cat_access = split(':',$group->{'cat_access'});
			my %is_cat = ();
			for (@cat_access) { $is_cat{$_} = 1; }
			if (($is_cat{$catid}) || ($is_cat{'all'})) { $include=1; }
			if (in_array($rep->{'email'},@cat_reps)) { $include=0; }
			if ($rep->{'password'} =~ /\*LOCKED\*/) { $include=0; }
			if ($include == 1) { push @cat_reps, $rep->{'email'}; }
		}
	}
	}
	return @cat_reps;
}

#in_array($needle,@haystack);
sub in_array { my ($needle, @haystack) = @_; foreach (@haystack) { return 1 if $_ eq $needle }}

## Rest Of Code ##

$sendmail=get_sendmail();

#get email message
my $max_eml_size=10485760+$myconfig->{'attachment_size'};
while (<STDIN>) {
    #push @message, $_;
    $eml .= $_;
    if (length($eml) > $max_eml_size) { print $lang_msg_toobig; exit; }
}
if (!$eml) { print $lang_no_msg; exit; }
if ($eml) { @message = split("\n", $eml); }

#SpamAssassin start
my $is_spam;
if ($spamassassin_enable == 1) {
	#email size check - if its too big, the script will just exit
	if (length($eml) > $myconfig->{'attachment_size'} * 1024) { exit; }

	  #use Mail::SpamAssassin;
	  my $spamtest = Mail::SpamAssassin->new();
	  my $mail = $spamtest->parse($eml);
	  my $status = $spamtest->check($mail);

	  if ($status->is_spam()) { $is_spam = 1; }

	  $eml = $status->rewrite_mail();

	  $status->finish();
	  $mail->finish();
}
#SpamAssassin end

#get email headers
@headers=get_headers(@message);
%header=set_headers(@headers);

#save email headers
my $eml_headers;
if ($myconfig->{'save_headers'}) {
	#This solution gives you just the true headers instead of the entire email...
	foreach (@headers) {
		chomp;
		$eml_headers .= "$_\n";
	}
}
if ($header{'precedence'}) {
	exit 0 if $header{'precedence'} =~ /(bulk|list|junk)/i;
}

if ($header{'subject'}) { $eml_subj = trim($header{'subject'}); }
else { $eml_subj = $no_subject; }
$eml_subj = decode_mimestring($eml_subj,$mailcharset);
$eml_subj = decode_qp($eml_subj);
$eml_subj =~ s/\s*($subject_re):s*//gi if ($eml_subj);
$eml_subj = $no_subject if (!$eml_subj);

if ($header{'reply-to'}) { $sent_from = trim($header{'reply-to'}); } #rfc says use reply-to if supplied
else { $sent_from = trim($header{'from'}); }
if (!$sent_from && $header{'return-path'}) { $sent_from = trim($header{'return-path'}); } #last resort

if (!$sent_from) { print $lang_no_from_param; exit; }

$sent_from = decode_mimestring($sent_from,$mailcharset);
($name_from,$email_from) = get_email($sent_from);
if ($name_from) { $sent_from='"'.$name_from.'"'." <$email_from>"; }
else { $sent_from=$email_from; }

my $name_to;
my $email_to;
if ($header{'to'}) {
	$sent_to = decode_mimestring(trim($header{'to'}),$mailcharset);
	($name_to,$email_to) = get_email($sent_to);
}
else { print $lang_no_to_param; exit; }

my $name_cc;
my $email_cc;
if ($header{'cc'}) {
	$sent_cc = decode_mimestring(trim($header{'cc'}),$mailcharset);
	($name_cc,$email_cc) = get_email($sent_cc);
}

#is email from and to the same?
#if ($email_from eq $email_to) { print "email from and email to are the same...\n"; exit; }

#is email address banned?
$sth = $dbh->prepare("SELECT * FROM ".$db_table_prefix."banlist WHERE value LIKE '%@%'");
$sth->execute();
my $banlist = undef;
while (my $ban = $sth->fetchrow_hashref) {
    $banlist = $ban->{'value'};
    if ($email_from =~ /$banlist/i) { exit; }
}

#get GMT/UTC
#use POSIX;
my @gm = gmtime();
my $gmtime = POSIX::mktime($gm[0], $gm[1], $gm[2], $gm[3], $gm[4], $gm[5]);
#used with the flood protection (not sure what the -75 is for)
my @oldgm = gmtime(time()-75);
my $gmoldtime = $oldgm[5] . $oldgm[4] . $oldgm[3] . $oldgm[2] . $oldgm[1] . $oldgm[0];

# flood protection, find all posts made by this email address in the last X minutes
my $smoog_email=$email_from;
my $smoog_mysqltime = $gmtime - 75;
my $smoog_earlieststamp = -1;
my $smoog_messagesfound = 0;
my $smoog_query = "SELECT * FROM ".$db_table_tickets." WHERE email='$smoog_email' ORDER BY timestamp ASC";
my $smoog_tickets;
my $smoog_messages;

$smoog_tickets = $dbh->prepare($smoog_query);
$smoog_tickets->execute();

while (my $smoog_t = $smoog_tickets->fetchrow_hashref) {
	  $smoog_query = "SELECT UNIX_TIMESTAMP(timestamp) as unixtimestamp FROM ".$db_table_prefix."messages WHERE ticket='".$smoog_t->{'ID'}."' AND timestamp > '$gmoldtime'";
      $smoog_messages = $dbh->prepare($smoog_query);
      $smoog_messages->execute();
      while (my $m = $smoog_messages->fetchrow_hashref) {
      		if ($smoog_earlieststamp == -1 || $smoog_earlieststamp > $m->{'unixtimestamp'}) {
      			$smoog_earlieststamp = $m->{'unixtimestamp'};
      		}
			$smoog_messagesfound++;
     }
}

# done figuring out recent message count, now lets check the rate.
my $smoog_msgrate = (60 * $smoog_messagesfound) / ($gmtime - $smoog_earlieststamp); 
if (!length($smoog_email) || $smoog_msgrate > $flood_msg_rate) { exit; }
# end flood protection

#get the ticket number from the subject
if ($eml_subj =~ m/$ticket_format/i) { $ticket = trim($1); }

#strips the ticket number from the subject - don't think it's needed...
$eml_subj =~ s/$ticket_format//i;

#SpamAssassin Subject Mod START
if ($eml_subj !~ /^\Q$spamword\E/) { $eml_subj = $spamword.' '.$eml_subj if ($is_spam); }
#SpamAssassin Subject Mod END

#format email priority to ticket priority.
my $pri=2;
if ($header{'x-priority'}) {
	$pri = substr trim($header{'x-priority'}),0,1;
	if ($pri == 1 || $pri == 2) { $pri = 3; }
	elsif ($pri == 3) { $pri = 2; }
	elsif ($pri == 4 || $pri == 5) { $pri = 1; }
	else { $pri = 2; }
}

#blank the body
$body='';
our @files;
our @codes;
our $error;
our $noreply;
my $body_text;
my $body_html;
###
my @content=();
push @content, $eml;
while (@content) {
	my $tmp_eml = shift @content;
	if ($tmp_eml) { $tmp_eml = trim($tmp_eml); }
	my $msg='';
	my $type='';
	my %tmp_header=();
	my @tmp_lines = split("\n", $tmp_eml);
	my @tmp_headers = get_headers(@tmp_lines);
	if (@tmp_headers) {
		%tmp_header=set_headers(@tmp_headers);
		for (my $i = $#tmp_headers + 1; $i <= $#tmp_lines; $i++) { $msg .= $tmp_lines[$i]."\n"; }
	}

	my $tmp_boundary;
	# Is there boundaries?
	#if ($tmp_header{'content-type'} && $tmp_header{'content-type'} =~ m/.*multipart\/.*boundary=([^;\s]+)/i) {
	if ($tmp_header{'content-type'} && $tmp_header{'content-type'} =~ m/boundary=([^;\s]+)/gi) {
		$tmp_boundary = $1;
		$tmp_boundary =~ s/\"//g;
		if ($tmp_boundary) {
			$tmp_boundary = '--'.$tmp_boundary;
			my @tmp_content = split($tmp_boundary, $tmp_eml);
			if ($#tmp_content>0) { push @content, @tmp_content; }
		}
	}

	# Decoding
	if ($tmp_header{'content-transfer-encoding'}) {
		if (trim($tmp_header{'content-transfer-encoding'}) eq 'quoted-printable') { $msg=decode_qp($msg); }
		if (trim($tmp_header{'content-transfer-encoding'}) eq 'base64') { $msg=decode_base64($msg); }
	}
	if ($tmp_header{'content-type'} && $tmp_header{'content-type'} =~ m/charset=([^;\s]+)/is) { $msg=convert_charset($msg,$1,$mailcharset); }
	# Is this an attachment?
	if ($tmp_header{'content-type'} && $tmp_header{'content-disposition'} && $tmp_header{'content-disposition'} =~ m/attachment.*/is && $tmp_header{'content-type'} =~ m/name=([^;\n\r]+)/is) {
		my $filename = trim($1);
		$filename =~ s/\"//g;
		$filename = decode_mimestring($filename,$mailcharset);
		$filename = basename($filename);
		if (!$filename) { $filename='attachment.txt'; }

		my $filext;
		$filext = (lc($filename) =~ /(\.[^.]+)\z/)[0];
		my @filetypes = ();
		if ($myconfig->{'filetypes'}) { @filetypes = split(';',$myconfig->{'filetypes'}); }
		if (@filetypes) {
			my %is_filext = ();
			for (@filetypes) { $is_filext{lc($_)} = 1; }
			if (!$is_filext{$filext}) { $error .= "'$filename': ".$lang_error_filext."\n"; }
		}
		if (length($msg) >= $myconfig->{'attachment_size'}) { $error .= "'$filename': ".$lang_error_size."\n"; }
	
		if (!$error) {
			push @files, $filename;
			push @codes, $msg;
		}
	}
	elsif ($tmp_header{'content-type'} && $tmp_header{'content-type'} =~ m/((text|message)\/[^;\s]+)/is) {
			$type=trim(lc($1));
			my $newfile;
			if (($type eq 'text/plain') && ($msg =~ /Spam detection software/) && $tmp_header{'content-disposition'} && $tmp_header{'content-disposition'} eq 'inline') { $newfile = 'sa_headers.txt'; }
			elsif ($type eq 'message/rfc822' && $tmp_header{'content-type'} =~ m/original/i) { $newfile = 'original.msg'; }
			elsif ($type eq 'message/rfc822' && $tmp_header{'subject'}) { $newfile = $tmp_header{'subject'}.'.msg'; }
			elsif ($type eq 'message/rfc822') { $newfile = 'message.msg'; }
			elsif ($type eq 'message/delivery-status') { $newfile = 'details.txt'; }
			#elsif ($tmp_header{'content-disposition'}) { $newfile = 'message.txt'; }
			if ($newfile) {
				push @files, $newfile;
				push @codes, $msg;
			}
			else {
				if ($type eq 'text/html') {
					#strip tags and trim
					if ($msg =~ m/<\s*body.*?\/?>(.*?)<\s*\/\s*body\s*\/?\s*>/gis) { $msg=$1; } # this strips out the body text if it exists
					$msg =~ s/<br>|<p>|<br \>/\n/gi;	 #create new lines
					$msg =~ s/<.+?\n*.*?>//sg;	#remove all <> html tages
					$msg =~ s/\&nbsp;/ /g;	 #remove html &nbsp characters
					#$msg =~ s/(\n|\r|(\n\r)|(\r\n)){4}/\n/g; #remove any extra new lines
					#$msg =~ s/(\s\s){4}/$1/g; #remove any extra whitespace
					$msg = trim($msg); # trim message
					$msg = HTML::Entities::decode_entities($msg);
					$body = "$msg\n";
				}
				elsif ($type eq 'text/plain') { $body = "$msg\n"; }
				elsif (!$body) { $body = "$msg\n"; }
			}
			if (($type eq 'message/delivery-status') && ($msg =~ m/Action: failed/is)) { $noreply=1; }
	}
	elsif (!$tmp_header{'content-type'} && !$body) { $body = "$msg\n"; }
	elsif (!$body) { $body = "$msg\n"; }
}
###
if ($error) { $body .= "\n".$lang_error.": ".$error; }
#if ($debug) { print $body; use Data::Dumper; print Dumper(\@files); exit; }

#get category id
$sth = $dbh->prepare("SELECT * FROM ".$db_table_prefix."categories");
$sth->execute();
my $catid;
while (my $cat_rows = $sth->fetchrow_hashref) {
	if ($cat_rows->{email}) {
		if ($email_cc && $cat_rows->{'email'} =~ /$email_cc/i) { $catid = $cat_rows->{'ID'}; }
		if ($email_to && $cat_rows->{'email'} =~ /$email_to/i) { $catid = $cat_rows->{'ID'}; }
	}
}
if (!$catid) { $catid = 1; }

#get category vars
$sth = $dbh->prepare("SELECT * FROM ".$db_table_prefix."categories");
$sth->execute();
while (my $cat_row = $sth->fetchrow_hashref) {
	if ($cat_row->{'ID'} == $catid) {
		$email_to = $cat_row->{'email'};
		$name_to = $cat_row->{'name'};
		$sent_to = "$name_to <$email_to>";
		$signature = $cat_row->{'signature'};
		$category = $cat_row->{'name'};
	}
}

## antispam addon - this part checks the subject and body
my $antispam_bypass = 0;
if ($ticket) {
	$sth = $dbh->prepare("SELECT ID FROM ".$db_table_tickets." WHERE ID = ".$ticket);
	$sth->execute();
	if ($sth->fetchrow_hashref) { $antispam_bypass = 1; }
}
$sth = $dbh->prepare("SELECT * FROM ".$db_table_prefix."banlist WHERE value NOT LIKE '%@%'");
$sth->execute();
my $antispam_spambody = $name_from.$eml_subj.$body;
if (!($antispam_spambody =~ /$ANTISPAM_MAGICWORD/i) && ($antispam_bypass == 0)) {
	while (my $ban = $sth->fetchrow_hashref) {
	    $banlist = $ban->{'value'};
	    $banlist =~ s/\!//g;
	    if ($antispam_spambody =~ /\Q$banlist\E/i) {
	      if (!$ANTISPAM_MAGICWORD || !$ANTISPAM_MSG) { $noreply=1; }
	    	if (!$ANTISPAM_EMAIL) { $ANTISPAM_EMAIL=$sent_to; }
	    	$ANTISPAM_MSG =~ s/{MAGICWORD}/$ANTISPAM_MAGICWORD/g;
	    	if (!$noreply) { sendmail($sent_from, $ANTISPAM_EMAIL, $ANTISPAM_SUBJECT, $ANTISPAM_MSG); }
		   	exit;
	    }
	}
}
## end antispam

#remove original message
if ($myconfig->{'remove_original'} && $myconfig->{'remove_tag'} && index($body, $myconfig->{'remove_tag'})) {
    $remove_tag = $myconfig->{'remove_tag'};
    $body =~ s/>? ?\Q$remove_tag\E.*//is;
    $body =~ s/>? ?-*[\n|\s|\0]?Original Message[\n|\s|\0]?-*.*//s;
}

#add signature/remove tag
if ($signature) { $signature = $presig.$signature; }
if ($myconfig->{'remove_original'}) { $remove_tag = $myconfig->{'remove_tag'} . "\n\n"; }

#add message for ticket if it is valid
if (is_valid($ticket)) {
    $sth = $dbh->prepare("UPDATE ".$db_table_tickets." SET status = 'open' WHERE ID=$ticket");
    $sth->execute();

		$sth = $dbh->prepare("INSERT INTO ".$db_table_prefix."messages (ticket, message, headers, timestamp) VALUES (?, ?, ?, UTC_TIMESTAMP())");
		$sth->execute($ticket, $body, $eml_headers);

    my $message_subj = keyword($myconfig->{'message_subj'});
    my $message_msg = keyword($myconfig->{'message_msg'});
    
  	if ($remove_tag) { $message_msg=$remove_tag.$message_msg; }
    if ($signature) { $message_msg=$message_msg.$signature; }

    if ($myconfig->{'message_response'}) {
        if (!$noreply) { sendmail($sent_from, $sent_to, $message_subj, $message_msg); }
    }
}
#create new ticket
else {
    $ticket=get_ticket(100000,999999);
    $sth = $dbh->prepare("SELECT COUNT(*) AS tq FROM ".$db_table_tickets." WHERE email='$email_from' AND status='open'");
    $sth->execute();
    my $row_hash_tq = $sth->fetchrow_hashref;
    my $tq = $row_hash_tq->{tq};
    if ($tq < $myconfig->{'ticket_max'}) {
        $sth = $dbh->prepare("SELECT UNIX_TIMESTAMP(timestamp) AS timestamp FROM ".$db_table_tickets." WHERE email='$email_from' ORDER BY timestamp DESC");
        $sth->execute();
        my $row_hash = $sth->fetchrow_hashref;
        my $interval;
        if ($row_hash->{'timestamp'}) {
        	$interval = time() - $row_hash->{'timestamp'};
        }
        my $ip;
        if ($header{'x-originating-ip'}) {
	        $ip = $header{'x-originating-ip'};
	        $ip =~ s/\[(.*)\]/$1/;
      	}
        if (!$ip) { $ip=''; }

        $sth = $dbh->prepare("INSERT INTO ".$db_table_tickets." (subject, name, email, cat, status, ID, priority, ip, timestamp) VALUES (?, ?, ?, ?, ?, ?, ?, ?, UTC_TIMESTAMP())") or die $dbh->errstr;
        $sth->execute($eml_subj, $name_from, $email_from, $catid, 'open', $ticket, $pri, $ip) or die $dbh->errstr;

        $sth = $dbh->prepare("INSERT INTO ".$db_table_prefix."messages (ticket, message, headers, timestamp) VALUES (?, ?, ?, UTC_TIMESTAMP())") or die $dbh->errstr;
        $sth->execute($ticket, $body, $eml_headers) or die $dbh->errstr;

        if ($interval && $myconfig->{'ticket_response'} && $interval >= $myconfig->{'min_interval'}) {
            my $ticket_subj = keyword($myconfig->{'ticket_subj'});
            my $ticket_msg = keyword($myconfig->{'ticket_msg'});
            if ($remove_tag) { $ticket_msg=$remove_tag.$ticket_msg; }
            if ($signature) { $ticket_msg=$ticket_msg.$signature; }
            if ($myconfig->{'ticket_response'}) {
                if (!$noreply) { sendmail($sent_from,$sent_to,$ticket_subj,$ticket_msg); }
            }
        }
    }
    #if over ticket limit
    else {
        if ($myconfig->{'limit_response'}) {
	        my $limit_subj = keyword($myconfig->{'limit_subj'});
	        my $limit_msg = keyword($myconfig->{'limit_msg'});
	        if ($signature) { $limit_msg=$limit_msg.$signature; }
	        if (!$noreply) { sendmail($sent_from, $myconfig->{'limit_email'}, $limit_subj, $limit_msg); }
	        if ($debug) { print $myconfig->{'limit_msg'}; }
	        exit;
        }
    }
}

if ($ticket) {
	#upload attachments
	if ($myconfig->{'accept_attachments'} && (-w $myconfig->{'attachment_dir'})) {
	    $sth = $dbh->prepare("SELECT LAST_INSERT_ID() AS iid");
	    $sth->execute();
	    my $row_hash = $sth->fetchrow_hashref;
	    my $iid = $row_hash->{'iid'};
	
	    for (my $i = 0; $i <= $#files; ++$i) {
	        if (length($codes[$i]) < $myconfig->{'attachment_size'}) {
		        my $id = int(rand(999999-100000+1))+100000;
		        my $file_name = $id.'_'.$files[$i];
		        my $file = $myconfig->{'attachment_dir'}.$file_name;
		        write_file($file,$codes[$i]);
		        if (-e $file) {
			        $sth = $dbh->prepare("INSERT INTO ".$db_table_prefix."attachments (ticket, ref, filename, type) VALUES (?,?,?,?)");
			        $sth->execute($ticket, $iid, $file_name, 'q') or die $dbh->errstr;
		      	}
	        }
	    }
	}
	
	#alert reps
	if ($myconfig->{'alert_new'}) {
		my @cat_reps;
		my @alert_emails;
		my @alert_users;

		#get_alert_emails
		@alert_users=split(',',$myconfig->{'alert_user'});
		foreach (@alert_users) {
			if (!in_array($_,@alert_emails)) {
				push @alert_emails, $_;
			}
		}
		@cat_reps=get_cat_reps($catid);
		foreach (@cat_reps) {
			if (!in_array($_,@alert_emails)) {
				push @alert_emails, $_;
			}
		}
			
		my $alert_subj = keyword($myconfig->{'alert_subj'});
		my $alert_msg = keyword($myconfig->{'alert_msg'});
		#sendmail($myconfig->{'alert_user'}, $myconfig->{'alert_email'}, $myconfig->{'alert_subj'}, $myconfig->{'alert_msg'});
		foreach (@alert_emails) {
			sendmail($_, $myconfig->{'alert_email'}, $alert_subj, $alert_msg);
		}
	}
	if ($debug) { print "$ticket\n"; }
}
if ($writeml) {
  my $rand=int(rand(999999-100000+1))+100000; write_file("eml-$ticket-$rand.txt",$eml);
}
$sth->finish();
$dbh->disconnect();

#EOF