#!/usr/bin/perl
use strict;
use FindBin;
use lib $FindBin::Bin;
#use lib '/data/httpd/lib/perl';
use ot_const qw( %ot &res );
use ot_dblib qw( &db_connect &db_disconnect &db_get_msg &db_insert_receive
&db_insert_cmd );
use ot_maillib qw( &debug &mail &mailerror );
use ot_execute qw( &execute );

=head1 NAME

ot_sender - sends comments to mailing lists

=head1 SYNOPSIS

Reads table par_mail and sends all mails to corresponsing mailinglists.

=head1 DESCRIPTION

The table par_mail is read, and all mails are send to the corresponding
mailing lists. This function is usually called by a cron job.

=cut

my $admin = $ot{listmaster};

my @mailtext;	# mail text array
my @mailaddr;
my %seen = ();
my @pid;	# pers_id array
my $p;
my @mid;	# memb_id array
my $m;
my $email;
my $pjname;
my $pjnlow;
my $addr_to;
my $text,
my $txt;
my $dbh;	# database handle
my $query;	# query object
my $qstr;	# sql query string
my $row;	# one fetched row
my $pid;	# project id (got from list name)
my $subj;	# subject of the header
my %rec;
my $rec_id;
my $cmd_id;
my @all_id;
my $msg_id;
my %msg;
my $dat;
my $res;
my $i;

#### html2mail
sub html2mail {
	$_ = pop( @_ );
#	s/\s*\n\s*/ /g;		# replace newlines
	s/<li>/\n- /gi;		# replace <li>'s with '\n- '
#	s/<[^>]*>//g;		# elim tags - kills email address:-(
#	s/\\('|"|\\)/$1/g;	# strip backslashes
	s/&auml;/ä/g;
	s/&Auml;/Ä/g;
	s/&ouml;/ö/g;
	s/&Ouml;/Ö/g;
	s/&uuml;/ü/g;
	s/&Uuml;/Ü/g;
	s/&szlig;/ß/g;
	return $_;
}

#################################################################################

debug( "\n***** Start ot_sender: ".localtime, 1 );

$dbh = db_connect;	# database handle
%msg = db_get_msg( $dbh, 10, 'de' );

$qstr = "select proj_id, memb_id from par_mail group by proj_id, memb_id";
$query = $dbh->prepare( $qstr );
$query->execute();
while ($row = $query->fetchrow_hashref() ) {
	push( @pid, $row->{'proj_id'} );
	push( @mid, $row->{'memb_id'} );
}
$query->finish();

foreach $m ( @mid ){
	$p = shift( @pid );	# take from end
	push( @pid, $p );	# put on top
	debug( "pid=$p, mid=$m", 1 );
	$i = 0;
	$txt = '';
	$qstr = "select first_n, last_n, email from member ";
	$qstr .= "where memb_id = $m";
	$query = $dbh->prepare( $qstr );
	$query->execute();
	$row = $query->fetchrow_hashref();
	$query->finish();
	if ( !( $row->{'first_n'} )) {
		db_disconnect( $dbh );
		mailerror( "ot_sender: select 1 returns empty set:\n$qstr" );
		exit;
	}
	$email = "\"$row->{'first_n'} $row->{'last_n'}\" ";
	$email .= "<$row->{'email'}>";
	$query->finish();
	$qstr = "select p.subj, p.text, p.ctext, t.titlabb ";
	$qstr .= "from par_mail p, metatext t where p.proj_id = t.proj_id ";
	$qstr .= "and p.proj_id = $p and p.memb_id = $m and t.status = 'act'";
	$query = $dbh->prepare( $qstr );
	$query->execute();
	while ($row = $query->fetchrow_hashref() ) {
		if( $i == 0 ){
			$pjname = $row->{'titlabb'};
			$pjnlow = $pjname;
			$pjnlow =~ tr/[A-Z]/[a-z]/;	# transform to lower case
			$subj = $row->{'subj'};
			$addr_to = qq("$msg{1} $pjname" <$pjnlow\@$ot{maildom}>);
			if(( $m == 0 ) && ( substr( $row->{'text'}, 0, 7 ) ne 'Ano Nym' )){
				$_ = $row->{'text'};
				/(<.+\@.+>)/;
				$email = "$msg{6} $1";	### noch email rausfiddeln
			}
		}
		$text = html2mail( $row->{'text'} );
		$txt .= $text.html2mail( $row->{'ctext'} )."\n";
		$i++;
	}
	$query->finish();
	if( $i > 1 ) { $subj = "$msg{1} '$pjname'" };
	$msg_id = 'sender'.int(rand( 10000000000 ));
	push( @all_id, $msg_id );
	$dat = localtime;
	$subj =~ s/(['"])/\\$1/g;	# backslash quotes
	%rec = (
		msg_id		=> $msg_id,
		proj_addr	=> $pjnlow,
		proj_id		=> $p,
		addr_to		=> $addr_to,
		subject		=> $subj,
		sender		=> $email,
		memb_id		=> $m,
		send		=> $dat,
		body		=> 'unused'
	);
	if( !( $rec_id = db_insert_receive( $dbh, %rec ))) {
		db_disconnect( $dbh );
		mailerror( "ot_sender: error inserting receive record" );
		exit;
	}
	$cmd_id = int( rand( 1000000000 ));
	$txt =~ s/(['"])/\\$1/g;	# backslash quotes
	if( !( db_insert_cmd( $dbh, $rec_id, $cmd_id, 1, 'maillist', $txt ))) {
		db_disconnect( $dbh );
		mailerror( "ot_sender: error inserting command record" );
		exit;
	}
}

foreach $msg_id ( @all_id ){
	$res = execute( $msg_id );
	debug( 'execute-result: '.res( $res ), 1 );
}

$qstr = "insert into par_mail_sav select * from par_mail";
$query = $dbh->prepare( $qstr );
$query->execute();
$qstr = "delete from par_mail";
$query = $dbh->prepare( $qstr );
$query->execute();
$qstr = "delete from session";
$query = $dbh->prepare( $qstr );
$query->execute();
db_disconnect( $dbh );
