#!/bin/bash
#
# $Id: mail_domain_by_nondomain_user,v 1.2 2002/03/18 22:47:35 syatskevich Exp $
#
#   $DOMAINNAME   
#

#  
if [ "x$PSQL" = "x" ]; then
	echo "   (PSQL).  $0 "
	exit 1
fi

# DOMAINNAME,  ,  
# ,   
. /etc/sysconfig/network
if [ "x$DOMAINNAME" = "x" ]; then
	echo "   .  $0 "
fi

#    
if [ "x$FILTER" != "x" ]; then
	WHERE_FILTER="WHERE ($FILTER)"
	AND_FILTER="AND ($FILTER)"
fi

if [ "x$KB" = "x" ]; then
	KB=1
fi

# $ND_TOP
if [ "x$ND_TOP" != "x" ]; then
	LIMIT_ND_TOP="LIMIT $ND_TOP"
fi

QUERY="
SELECT
	sender AS email,
	count AS from_count, bytes AS from_bytes,
	0     AS to_count  , 0     AS to_bytes

FROM mail_log
WHERE sender   NOT LIKE '%$DOMAINNAME' AND
      reciever LIKE     '%$DOMAINNAME' $AND_FILTER

UNION ALL

SELECT
	reciever AS email,
	0     AS from_count, 0     AS from_bytes,
	count AS to_count  , bytes AS to_bytes

FROM mail_log
WHERE reciever NOT LIKE '%$DOMAINNAME' AND
      sender   LIKE     '%$DOMAINNAME' $AND_FILTER"

QUERY="
SELECT
	email                        AS \"E-Mail\",
	SUM (from_count)             AS \"-\",
	ROUND (SUM (from_bytes)/$KB, 0) AS \" \",
	SUM (to_count)               AS \"-\",
	ROUND (SUM (to_bytes)/$KB, 0)   AS \" \",
	SUM (from_count + to_count)  AS \"-\",
	ROUND (SUM (from_bytes + to_bytes)/$KB, 0)
	                             AS \"\"

FROM ($QUERY) subselect
GROUP BY email ORDER BY \"\" DESC $LIMIT_ND_TOP"

echo "**************************************************************"
echo "*   $DOMAINNAME,  "
echo "*  "
echo "*"
echo "*    '-'  "
echo "*"
echo "*  :"
echo "*     = $KB"
echo "*            = $ND_TOP"
echo "**************************************************************"
$PSQL -c "$QUERY"
