#!/bin/bash
#
# $Id: mail_domain_by_domain_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

QUERY="
SELECT
	sender AS user_name ,
	0     AS get_count , 0     AS get_bytes,
	count AS post_count, bytes AS post_bytes

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

UNION ALL

SELECT
	reciever AS user_name ,
	count AS get_count , bytes AS get_bytes,
	0     AS post_count, 0     AS post_bytes

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

QUERY="
SELECT
	name                         AS \"\",
	SUM (get_count)              AS \"-\",
	ROUND (SUM (get_bytes)/$KB, 0)  AS \"\",
	SUM (post_count)             AS \"-\",
	ROUND (SUM (post_bytes)/$KB, 0) AS \"\",
	SUM (get_count + post_count) AS \"-\",
	ROUND (SUM (get_bytes + post_bytes)/$KB, 0)
	                             AS \"\"

FROM ($QUERY) subselect, names
WHERE user_name = data
GROUP BY name

UNION ALL

SELECT
	user_name                    AS \"\",
	SUM (get_count)              AS \"-\",
	ROUND (SUM (get_bytes)/$KB, 0)  AS \"\",
	SUM (post_count)             AS \"-\",
	ROUND (SUM (post_bytes)/$KB, 0) AS \"\",
	SUM (get_count + post_count) AS \"-\",
	ROUND (SUM (get_bytes + post_bytes)/$KB, 0)
	                             AS \"\"

FROM ($QUERY) subselect
WHERE user_name NOT IN (SELECT data FROM names)
GROUP BY user_name

ORDER BY \"\""

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