#!/usr/bin/perl -w
#########################################################################
## netstat-montrly-mail - 1.01 / 07.05.2003/
## (c) 2002 Boril Yonchev <bashbug@users.sourceforge.net>
##
## Put netacct-monthly-mail.pl into /etc/cron.monthly
##
## This script generated a mail with montly traffic for every user 
## with email addres into his info
#########################################################################
##
## If you haven't Mysql, Number::Format or Time::Piece 
## modules then install with
## perl -MCPAN -e 'install Number::Format'
## perl -MCPAN -e 'install Time::Piece'
## perl -MCPAN -e 'install Bundle::Mysql'  or with
## perl -MCPAN -e 'install Mysql'
##
#

use strict;
use Mysql;
use Number::Format;
use Time::Piece;

my $host = "localhost";

my $netacct_dbname = "netacct";
my $netacct_user = "acct";
my $netacct_pass = "acct_password";
my $sendmail = "/usr/sbin/sendmail";

my $from_email = "NetStat <traffic\@your.domain.com>";
my $subject = "Monthly Traffic %s - %s";

my $charset = "windows-1251";
my $font_family = "verdana, arial, helvetica, geneva, sans-serif";
my $page_bgcolor = "#F5F5F5";

my $Head = "Statistic for %s";
my $Remark = "This is automaticly generated message. Please do not reply";
my $VAT_comment = "Prices are in USD";

#********************* Don't edit any more **************************

my $x = new Number::Format;
my $t = new Time::Piece;

my $mnt = $t->mon;
   $mnt--;
my $from = $t->year."-".$mnt."-01";
my $till = $t->year."-".$mnt."-31";

my $dbh1 = Mysql->connect($host, $netacct_dbname, $netacct_user, $netacct_pass);
if(!$dbh1) {print "MySQL problems with $netacct_dbname\n"; exit;}

my $dbh2 = Mysql->connect($host, $netacct_dbname, $netacct_user, $netacct_pass);
if(!$dbh2) {print "MySQL problems with $netacct_dbname\n"; exit;}

my ($query,%cln,$clients,$rows,%row);
my ($cols,$date);
my $i=0;

my $total_peering=0;
my $total_trafic=0;
my $total_direct=0;
my $total_local=0;
my $total_all=0;
my $total_peering_price=0;
my $total_trafic_price=0;
my $total_direct_price=0;
my $total_local_price=0;
my $total_all_traffic=0;
my $total_all_price=0;
my $input_traffic_price=0;
my $output_traffic_price=0;
my $input_peering_price=0;
my $output_peering_price=0;
my $input_direct_price=0;
my $output_direct_price=0;
my $input_local_price=0;
my $output_local_price=0;


$query="SELECT uid,login,email,status FROM users WHERE email NOT LIKE ''";
$clients=$dbh1->query($query);

while(%cln=$clients->fetchhash)
{

	$query =  "SELECT DATE_FORMAT(t.time,'%d-%m-%Y') AS time,
     		SUM(t.input) AS input,SUM(t.output) AS output,
		SUM(t.peer_input) AS peer_input,
		SUM(t.peer_output) AS peer_output,
		SUM(t.direct_input) AS direct_input,
		SUM(t.direct_output) AS direct_output,
		SUM(t.local_input) AS local_input,
		SUM(t.local_output) AS local_output, 
		u.input_traffic_price,u.output_traffic_price,
		u.input_peering_price,u.output_peering_price,
		u.input_direct_price,u.output_direct_price,
		u.input_local_price,u.output_local_price, 
		u.status FROM traffic AS t,users_ip AS ui
		LEFT JOIN users AS u ON t.ip = ui.ip 
		WHERE u.uid=ui.uid AND u.uid=$cln{'uid'}
		AND t.time>='$from' 
		AND t.time<='$till 23:00:00'
		GROUP by TO_DAYS(t.time) ORDER BY t.time";

	  $date=localtime;

	  open (OUT,"|$sendmail -t")  || die();
	  print OUT "From: ".$from_email."\n";
	  print OUT "To: ".$cln{'email'}."\n";
#	  print OUT "To: by\@stemo.bg\n";
	  print OUT "Bcc: traf\@stemo.bg\n";
	  print OUT "Subject: ".sprintf($subject,$cln{'login'},$date)."\n";
	  print OUT "Content-Type: text/html; charset=\"".$charset."\";\n";
	  print OUT "Content-Transfer-Encoding: 7bit\n";
	  print OUT "MIME-Version: 1.0\n";
	  print OUT "<html>\n";
	  print OUT "<head>\n";
	  print OUT "<title>~ NetStat ~ </title>\n";
	  print OUT "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=$charset\">\n";
	  print OUT "<style type=\"text/css\">\n";
	  print OUT "BODY { font-family: $font_family; font-size: 8pt;}\n";
	  print OUT "TH { font-family: $font_family; font-size: 8pt; font-weight: bold; background-color: #D3DCE3;}\n";
	  print OUT "TD { font-family: $font_family; font-size: 8pt;}\n";
	  print OUT "</style>\n";
	  print OUT "</head>\n";
	  print OUT "<body bgcolor=\"$page_bgcolor\">\n";
          print OUT "<p><small>NetStat Reporter ver. 1.01. (c) 2003</small></p>\n";
	  my $month=$mnt."-".$t->year;
	  
	  print OUT "<h4>".sprintf($Head,$month)."</h4>\n";
	  print OUT $Remark."<br><br>\n";
					  
				  
	$cols=($cln{'status'} ne 'client')?17:5;
	
	print OUT "<table BORDER=\"0\" CELLSPACING=\"2\" CELLPADDING=\"2\">\n";
	print OUT "<tr bgcolor=#000000><td colspan=".$cols."></td></tr>\n";
	print OUT "<tr>\n";
	print OUT "<th>$cln{'login'}</th>\n";	
	print OUT "<th colspan=4><small>Международен</small></th>\n";

	if ($cln{'status'} ne 'client')
	{
	  print OUT "<th colspan=4><small>БГ пееринг</small></th>\n";
	  print OUT "<th colspan=4><small>Директен</small></th>\n";
	  print OUT "<th colspan=4><small>Локален</small></th>\n";
	}
	print OUT "</tr>\n";
	print OUT "<tr>\n";
	print OUT "<th><small>Дата</small></th>\n";
	print OUT "<th><small>Вход<br>B/s</small></th>\n";
	print OUT "<th><small>Изход<br>B/s</small></th>\n";
	print OUT "<th><small>Сума<br>B/s</small></th>\n";
	print OUT "<th><small>Цена<br>\$/GB</small></th>\n";

	if ($cln{'status'} ne 'client')
        {
		print OUT "<th><small>Вход<br>B/s</small></th>\n";
	        print OUT "<th><small>Изход<br>B/s</small></th>\n";
	        print OUT "<th><small>Сума<br>B/s</small></th>\n";
	        print OUT "<th><small>Цена<br>\$/GB</small></th>\n";
	        print OUT "<th><small>Вход<br>B/s</small></th>\n";
	        print OUT "<th><small>Изход<br>B/s</small></th>\n";
	 	print OUT "<th><small>Сума<br>B/s</small></th>\n";
		print OUT "<th><small>Цена<br>\$/GB</small></th>\n";
		print OUT "<th><small>Вход<br>B/s</small></th>\n";
		print OUT "<th><small>Изход<br>B/s</small></th>\n";
		print OUT "<th><small>Сума<br>B/s</small></th>\n";
		print OUT "<th><small>Цена<br>\$/GB</small></th>\n";
	}
	print OUT "</tr>\n";
	print OUT "<tr bgcolor=#000000><td colspan=".$cols."></td></tr>\n";
	
	$rows=$dbh2->query($query);
	while(%row=$rows->fetchhash)
	{
		$input_traffic_price=$output_traffic_price=0;
		$input_peering_price=$output_peering_price=0;
		$input_direct_price=$output_direct_price=0;
		$input_local_price=$output_local_price=0;
		
		my $in_traffic_price=$row{'input_traffic_price'};
		my $out_traffic_price=$row{'output_traffic_price'};
		my $in_peering_price=$row{'input_peering_price'};
		my $out_peering_price=$row{'output_peering_price'};
		my $in_direct_price=$row{'input_direct_price'};
		my $out_direct_price=$row{'output_direct_price'};
		my $in_local_price=$row{'input_local_price'};
		my $out_local_price=$row{'output_local_price'};
		my $in=$row{'input'};
		my $out=$row{'output'};
		my $peer_in=$row{'peer_input'};
		my $peer_out=$row{'peer_output'};
		my $direct_in=$row{'direct_input'};
		my $direct_out=$row{'direct_output'};
		my $local_in=$row{'local_input'};
		my $local_out=$row{'local_output'};
		
		if ($in_traffic_price > 0)
		  {$input_traffic_price=$in*$in_traffic_price/1024000000;}
		if ($out_traffic_price > 0)
		  {$output_traffic_price=$out*$out_traffic_price/1024000000;}
		if ($in_peering_price > 0)
		  {$input_peering_price=$peer_in*$in_peering_price/1024000000;}
		if ($out_peering_price > 0)
		  {$output_peering_price=$peer_out*$out_peering_price/1024000000;}
		if ($in_direct_price > 0)
		  {$input_direct_price=$direct_in*$in_direct_price/1024000000;}
		if ($out_direct_price > 0)
		  {$output_direct_price=$direct_out*$out_direct_price/1024000000;}
		if ($in_local_price > 0)
		  {$input_local_price=$local_in*$in_local_price/1024000000;}
		if ($out_local_price > 0)
		  {$output_local_price=$local_out*$out_local_price/1024000000;}
										
        	my $sum_price_traf=$input_traffic_price+$output_traffic_price;
	        my $sum_price_peer=$input_peering_price+$output_peering_price;
	        my $sum_price_direct=$input_direct_price+$output_direct_price;
	        my $sum_price_local=$input_local_price+$output_local_price;
		
	        my $input=$row{'input'};
	        my $output=$row{'output'};
	        my $peer_input=$row{'peer_input'};
	        my $peer_output=$row{'peer_output'};
	        my $direct_input=$row{'direct_input'};
	        my $direct_output=$row{'direct_output'};
	        my $local_input=$row{'local_input'};
	        my $local_output=$row{'local_output'};
		
	        my $sum_traf=$input+$output;
	        my $sum_peer=$peer_input+$peer_output;
	        my $sum_direct=$direct_input+$direct_output;
	        my $sum_local=$local_input+$local_output;

		my $bgcolor=($i++ % 2)?"\#CCCCCC":"\#DDDDDD";
		
		print OUT "<tr bgcolor=$bgcolor align=\"right\">";
							  
		print OUT "<td nowrap>$row{'time'}</td>\n";
	        if ($cln{'status'} ne 'client')
		{
			print OUT "<td>".$x->format_number($input,0)."</td>\n";
			print OUT "<td>".$x->format_number($output,0)."</td>\n";
			print OUT "<td><b>".$x->format_number($sum_traf,0)."</b></td>\n";
			print OUT "<td>".$x->format_number($sum_price_traf,2)."</td>\n";

			print OUT "<td>".$x->format_number($peer_input,0)."</td>\n";
			print OUT "<td>".$x->format_number($peer_output,0)."</td>\n";
			print OUT "<td><b>".$x->format_number($sum_peer,0)."</b></td>\n";
			print OUT "<td>".$x->format_number($sum_price_peer,2)."</td>\n";
			
			print OUT "<td>".$x->format_number($direct_input,0)."</td>\n";
			print OUT "<td>".$x->format_number($direct_output,0)."</td>\n";
			print OUT "<td><b>".$x->format_number($sum_direct,0)."</b></td>\n";
			print OUT "<td>".$x->format_number($sum_price_direct,2)."</td>\n";

                        print OUT "<td>".$x->format_number($local_input,0)."</td>\n";
                        print OUT "<td>".$x->format_number($local_output,0)."</td>\n";
                        print OUT "<td><b>".$x->format_number($sum_local,0)."</b></td>\n";
                        print OUT "<td>".$x->format_number($sum_price_local,2)."</td>\n";

		        $total_trafic+=$sum_traf;
			$total_peering+=$sum_peer;
		        $total_direct+=$sum_direct;
			$total_local+=$sum_local;
	                
			$total_trafic_price+=$sum_price_traf;
	                $total_peering_price+=$sum_price_peer;
		        $total_direct_price+=$sum_price_direct;
		        $total_local_price+=$sum_price_local;
		}
		else
		{
		        my $sum_all_input=$input+$peer_input+$direct_input+$local_input;
			my $sum_all_output=$output+$peer_output+$direct_output+$local_output;
			my $sum_all_traf=$sum_all_input+$sum_all_output;
			my $sum_all_prices=$sum_price_traf+$sum_price_peer+$sum_price_direct+$sum_price_local;

			$total_all_traffic+=$sum_all_traf;
			$total_all_price+=$sum_all_prices;

	                print OUT "<td>".$x->format_number($sum_all_input,0)."</td>\n";
	                print OUT "<td>".$x->format_number($sum_all_output,0)."</td>\n";
	                print OUT "<td><b>".$x->format_number($sum_all_traf,0)."</b></td>\n";
	                print OUT "<td>".$x->format_number($sum_all_prices,2)."</td>\n";
		}
		print OUT "</tr>\n";		 
	}
  	print OUT "<tr bgcolor=#000000><td colspan=".$cols."></td></tr>\n";
    	print OUT "<tr>\n";
      	print OUT "<th></th>\n";
        if ($cln{'status'} ne 'client')
        {
	 print OUT "<th colspan=2><small>За периода</small></th>\n";
	 print OUT "<th>".$x->format_number($total_trafic,0)."</th>\n";
	 print OUT "<th>".$x->format_number($total_trafic_price,2)."</td>\n";
	 print OUT "<th colspan=2><small>За периода</small></th>\n";
	 print OUT "<th>".$x->format_number($total_peering,0)."</th>\n";
	 print OUT "<th>".$x->format_number($total_peering_price,2)."</th>\n";
	 print OUT "<th colspan=2><small>За периода</small></th>\n";
	 print OUT "<th>".$x->format_number($total_direct,0)."</th>\n";
	 print OUT "<th>".$x->format_number($total_direct_price,2)."</th>\n";
	 print OUT "<th colspan=2><small>За периода</small></th>\n";
	 print OUT "<th>".$x->format_number($total_local,0)."</th>\n";
	 print OUT "<th>".$x->format_number($total_local_price,2)."</th>\n";
	}
	else
	{
	 print OUT "<th colspan=2><small>За периода</small></th>\n";
	 print OUT "<th>".$x->format_number($total_all_traffic,0)."</th>\n";
	 print OUT "<th>".$x->format_number($total_all_price,2)."</th>\n";
	}
		
	print OUT "</tr>\n";
	print OUT "<tr bgcolor=#000000><td colspan=".$cols."></td></tr>\n";
	print OUT "</table>\n";
	print OUT "<br>".$Remark."<br>\n";
	print OUT "<p><strong>".$VAT_comment."</strong></p>\n";
	print OUT "</body>\n";
	print OUT "</html>\n";
	close ( OUT );

	 $total_peering=0;
	 $total_trafic=0;
	 $total_direct=0;
	 $total_local=0;
	 $total_all=0;
	 $total_peering_price=0;
	 $total_trafic_price=0;
	 $total_direct_price=0;
	 $total_local_price=0;
	 $total_all_traffic=0;
	 $total_all_price=0;
	 $input_traffic_price=0;
	 $output_traffic_price=0;
	 $input_peering_price=0;
	 $output_peering_price=0;
	 $input_direct_price=0;
	 $output_direct_price=0;
	 $input_local_price=0;
	 $output_local_price=0;
			   
}
