#!/usr/bin/perl -w
# $Id: daCode2usenet.pl,v 1.2 2001/09/20 23:34:56 netsabes Exp $
#
# This is a Perl script to forward news and comments from homesite which
# uses daCode to a usenet gateway.
#
# Put into your crontab something like:
#
# */1 * * * * /path/to/this/script.pl
#
# It requires few modules.
#
# Fabien Penso <penso@linuxfr.org>
#
my $login_sql = "SQLLOGIN";
my $passwd_sql = "SQLPASSWORD";
my $database = "SQLDATABASE";
# Change this if you don't run it every minute. Time is in seconds.
my $interval = "60";
my $newsgateway = "news.linuxfr.org";
my $newslogin = "usenetlogin";
my $newspassword = "usenetpassword";
my $newsgroup = "linuxfr.linuxfr-news";
# url of your homesite
my $nameurl = "http://linuxfr.org";
my $hostname = "zobe.linuxfr.org";
my $domain = "linuxfr.org";
########################################################################
# Don't change below unless you know what you are doing...
########################################################################

use strict;
use News::NNTPClient;
use DBI;
use HTML::Entities;

my $newstable = "news";
my $newsbodytable = "news_body";
my $sectiontable = "sections";
my $topictable = "topics";
my $authortable = "authors";
my $newsurlstable = "news_urls";
my $urlstable = "urls";
my $langtable= "lang";
my $commentstable = "comments";
my $sigtable = "sig";
my $userstable = "users";
my($TIME) = time - $interval;

my ($min_today,$hour_today,$lmday_today,$lmon_today,$lyear_today,$lwday_today) = 
		(localtime($TIME))[1,2,3,4,5,6];

$lmon_today+=1;
$lyear_today+=1900;

($lmday_today <= 9) ? $lmday_today = "0$lmday_today";
($lmon_today <= 9) ? $lmon_today = "0$lmon_today";
($hour_today <= 9) ? $hour_today = "0$hour_today";
($min_today <= 9) ? $min_today = "0$min_today";

my $dbh = DBI->connect("DBI:mysql:$database:localhost:3306", $login_sql,
	$passwd_sql, { RaiseError => 1}) or die "connecting : $DBI::errstr\n";

my $q = "SELECT $newstable.id,$newstable.title,$newstable.departement,".
  "$newsbodytable.body,$sectiontable.section,$topictable.topic,$authortable.name".
  ",$authortable.contact,$newstable.timestamp FROM $newstable,$newsbodytable,".
  "$sectiontable,$topictable,$authortable WHERE $newstable.state='1' AND ".
  "$topictable.id=$newstable.topic_id AND $sectiontable.id=$newstable.section_id ".
  "AND $newsbodytable.id=$newstable.id AND $authortable.id=$newstable.author_id ".
  "AND $newstable.timestamp LIKE \"$lyear_today$lmon_today$lmday_today$hour$min\%\" ".
  "ORDER BY $newstable.timestamp DESC";

my $c;
while (!($c = new News::NNTPClient($newsgateway,"",0))) {
	sleep 2;
}

$c->authinfo($newslogin,$newspassword);

my @news;
my $sth = $dbh->prepare($q);
my $rc=$sth->execute;
while (my (@row) = $sth->fetchrow) {
	push @news, \@row;
}
$sth->finish;

my $i=0;
while ($i<=$#news) {
	my $q = "SELECT $newsurlstable.name,$urlstanme.url,$langtable.lang FROM ".
	  "$newsurlstable,$urlstable,$langtable WHERE $newsurlstable.news_id='".
		${$news[$i]}[0]."' AND $urlstable.id=$newsurlstable.urls_id AND ".
		  "$langtable.id=$newsurlstable.lang_id";

   	my $sth = $dbh->prepare($q);
   	my $rc=$sth->execute;
   	if (!defined($rc)) {
       	exit 0;
   	}

	my $title = "[News] ".${$news[$i]}[4].": ".${$news[$i]}[1];

	my $body = "Auteur: ".${$news[$i]}[6];
	if (${$news[$i]}[7] !~ /^\s*$/i) {
		$body .= " ( ".${$news[$i]}[7]." )";
	}
	$body .= "\n";
	$body .= "Topic: ".${$news[$i]}[5]. "\n\n";

	use Text::Wrap qw($columns &wrap);
	my($columns)=72;
	my($bodyfinger)=wrap("    ", "",${$news[$i]}[3]);
	$body .= $bodyfinger . "\n\n";

	while (my (@row) = $sth->fetchrow) {
		$body .= "[$row[2]] - $row[0] ( $row[1] )\n";
	}
   	$sth->finish;

	$body = &cleanHTML($body);
	$title = &cleanHTML($title);

	$body .= "\n\n|> $nameurl/$lyear_today/$lmon_today/$lmday_today/".${$news[$i]}[0];
	$body .= "\n\n===========================================================================\n";
	$body .= "Ce message a été renvoyé par Moulinette 0.1\nSource: $nameurl\n";

	my @header = ("Newsgroups: $newsgroup", 
				  "Subject: $title",
				  "From: Moulinette\@$domain", 
				  "Message-ID: <plopnews". ${$news[$i]}[0] ."\@$hostname>",
				  "X-Moulinette: version 0.1 - Fabien Penso <penso\@linuxfr.org>",
				  "Approved: usenet\@linuxfr.org");
	my @body   = ("$body");

	$c->post(@header, "\n", @body);

	$i++;
}

my $q = "select $commentstable.id,$commentstable.subject,$commentstable.body,".
  "$commentstable.timestamp,$commentstable.news_id,$userstable.lname,".
  "$userstable.fname,$userstable.email,$userstable.login,".
  "$sigtable.sig,$commentstable.com_parent FROM $commentstable,$userstable,$sigtable ".
  "WHERE $userstable.id=$commentstable.user_id AND $sigtable.id=$commentstable.sig_id ".
  "AND $commentstable.timestamp LIKE \"$lyear_today$lmon_today$lmday_today$hour_today$min_today\%\" ".
  "ORDER BY $commentstable.timestamp";

my @comments;
$sth = $dbh->prepare($q);
$rc=$sth->execute;
while (my (@row) = $sth->fetchrow) {
	push @comments, \@row;
}
$sth->finish;

my $i=0;
while ($i<=$#comments) {
	
	my $references="<plopnews".${$comments[$i]}[4]."\@$hostname> ";

	$q = "SELECT id,com_parent,timestamp FROM $commentstable WHERE news_id='".
	  ${$comments[$i]}[4]."' ORDER BY timestamp";

	$sth = $dbh->prepare($q);
	$rc=$sth->execute;
	my @row_tmp;
	my %ref;
	while (my (@row) = $sth->fetchrow) {
		push @row_tmp, \@row;
		$ref{$row[0]} = $row[1];
	}
	$sth->finish();

	my $ok = 0;
	my $lastid=${$comments[$i]}[0];
	my $ref="";
	while($ok == 0) {
		if ($ref{ $lastid } != 0) {
			$ref = "<plopcomments".$ref{ $lastid }."\@$hostname> ".$ref;
			$lastid = $ref{ $lastid };
		} else {
			$ok=1;
			last;
		}
	}

	$references .= $ref;

	my $title = "[Comm] ".${$comments[$i]}[1];
	my $from  = "";

	if (defined(${$comments[$i]}[5]) && ${$comments[$i]}[5] !~ /^\s*$/i || 
		defined(${$comments[$i]}[6]) && ${$comments[$i]}[6] !~ /^\s*$/i) {
		$from .= ${$comments[$i]}[6] ." ". ${$comments[$i]}[5];
	} elsif (${$comments[$i]}[8] !~ /^\s*$/i) {
		$from .= ${$comments[$i]}[8];
	} else {
		$from .= "Anonyme";
	}

	if (${$comments[$i]}[7] !~ /^\s*$/i) {
		$from .= " <".${$comments[$i]}[7].">";
	} else {
		$from .= " <nomail\@$domain>";
	}

	use Text::Wrap qw($columns &wrap); 
	my($columns)=80;
	my($bodyfinger)=wrap("    ", "",${$comments[$i]}[2]);
	my $body .= $bodyfinger . "\n\n";

	if (${$comments[$i]}[9] !~ /^\s*$/i) {
		$body .= "-- \n".${$comments[$i]}[9]."\n";
	}

	$body = &cleanHTML($body);
	$title = &cleanHTML($title);

	$body .= "\n\n===========================================================================\n";
	$body .= "<url:$nameurl/comments/add.php3?news_id=".${$comments[$i]}[4].
			 "\&com_id=".${$comments[$i]}[0].">\n";

	$body .= "Ce message a été renvoyé par Moulinette 0.1\nSource: $nameurl\n";

	my @header = ("Newsgroups: $newsgroup",
				  "Subject: $title",
				  "From: $from",
				  "Message-ID: <plopcomments". ${$comments[$i]}[0] ."\@$hostname>",
				  "References: $references",
				  "X-Moulinette: version 0.1 - Fabien Penso <penso\@linuxfr.org>",
				  "Approved: usenet\@linuxfr.org");
	my @body   = ("$body");

	$c->post(@header, "\n", @body);

	$i++;

}

$c->quit();
$dbh->disconnect;

sub cleanHTML {
    my $tmp = $_[0];

    #$tmp =~ s/^M\n/\n/g;
    #$tmp =~ s/^M/\n/g;
    #$tmp =~ s/\s+/ /g;
    $tmp =~ s/^\&nbsp;//gi;
    $tmp = decode_entities($tmp);
    $tmp =~ s/\x91/'/g;
    $tmp =~ s/\x92/'/g;
    $tmp =~ s/\x93/"/g;
    $tmp =~ s/\x94/"/g;
    $tmp =~ s/\x96/-/g;
    $tmp =~ s/\x97/-/g;
    $tmp =~ s/\x85/\.\.\./g;
    $tmp =~ s/\x9c/oe/g;
    $tmp =~ s/\&\#8230;/\.\.\./ig;
    $tmp =~ s/\&\#8217;/'/ig;

    $tmp =~ s/<p>/\n\n/gi;
    $tmp =~ s/<p [^>]*?>/\n\n/gi;
    $tmp =~ s/<\/p>//gi;
    $tmp =~ s/<\/?i>//ig;
    $tmp =~ s/<\/?em>//ig;
    $tmp =~ s/<br>/\n/gi;
    $tmp =~ s/<script.*?>.*?<\/script>//gi;

    $tmp =~ s/<?<a href="?(?:mailto:)?([^">]*?)"?>([^<]*?)<\/a>>?/$2 \( $1 \)/ig;

    $tmp =~ s/<!--.*?-->//ig;
    $tmp =~ s/<\/?font[^>]*?>//ig;

	$tmp =~ s/<[^>]+?>//g;
	$tmp =~ s/\&lt;/</ig;
	$tmp =~ s/\&gt;/>/ig;

    $tmp =~ s/^\s*//g;
    $tmp =~ s/\s*$//g;

    return $tmp;

}