#!/bin/bash
#
# $Id: proxy_MxN,v 1.2 2002/03/18 22:47:35 syatskevich Exp $
#
#   -  M  "" .
#      N    .
#

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

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

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

# $MxN_M
if [ "x$MxN_M" = "x" ]; then
	MxN_M=4
fi
LIMIT_MxN_M="LIMIT $MxN_M"

# $MxN_N
if [ "x$MxN_N" = "x" ]; then
	MxN_N=4
fi
LIMIT_MxN_N="LIMIT $MxN_N"

CLIENT_LIST_QUERY="
	SELECT client_address FROM (
		SELECT client_address, SUM (total_bytes - hit_bytes) AS total
		FROM proxy_log $WHERE_FILTER
		GROUP BY client_address ORDER BY total DESC $LIMIT_MxN_M
	) subselect"

CLIENT_LIST=`$PSQLQ -c "$CLIENT_LIST_QUERY"`

echo "**************************************************************"
echo "*   -   \"\" "
echo "*"
echo "*  :"
echo "*                = $KB"
echo "*                         = $MxN_M"
echo "*        = $MxN_N"
echo "**************************************************************"

if [ "x$CLIENT_LIST" = "x" ]; then
	echo
	echo "      ."
	echo "       "
	echo

	exit 0
fi

for client in $CLIENT_LIST; do
	new=${#client_array[*]}
	client_array[$new]="$client"

	client_name_array[$new]=`$PSQLQ -c "SELECT name FROM names WHERE data = '$client'"`
	if [ "x${client_name_array[$new]}" = "x" ]; then
		client_name_array[$new]="$client"
	fi
done

GROUP_BY="host"
GROUP_AS=""

for ((count=0 ; ${#client_array[*]} - $count ; count=$count+1)); do

	HEADER=""
	HEADER2=""

	for ((count2=0 ; ${#client_array[*]} - $count2 ; count2=$count2+1)); do
		if [ $count2 -ne $count ]; then
			HEADER="$HEADER 0 AS \"${client_name_array[$count2]}\""
		else
			HEADER="$HEADER SUM (total_bytes - hit_bytes) AS \"${client_name_array[$count2]}\""
		fi

		HEADER2="$HEADER2 ROUND (SUM (\"${client_name_array[$count2]}\")/$KB, 0) AS \"${client_name_array[$count2]}\""

		if [ $count2 -lt `expr ${#client_array[*]} - 1` ]; then
			HEADER="$HEADER,"
			HEADER2="$HEADER2,"
		fi
	done

	QUERY="$QUERY SELECT * FROM (
		SELECT $GROUP_BY, $HEADER FROM proxy_log
		WHERE client_address = '${client_array[$count]}' $AND_FILTER
		GROUP BY $GROUP_BY
		ORDER BY \"${client_name_array[$count]}\" DESC $LIMIT_MxN_N) s$count"

	if [ $count -lt `expr ${#client_array[*]} - 1` ]; then
		QUERY="$QUERY UNION"
	fi
done

QUERY="
	SELECT $GROUP_BY AS \"$GROUP_AS\", $HEADER2 FROM ($QUERY) subselect
	GROUP BY $GROUP_BY ORDER BY $GROUP_BY"

$PSQL -c "$QUERY"
