#!/bin/bash
#
# $Id: proxy_by_client_for_hosts,v 1.1 2002/05/09 14:48:07 syatskevich Exp $
#
#   -     
#

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

if [ "x$PCH_HOSTS" = "x" ]; then
	echo "   "
	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

echo "**************************************************************"
echo "*   -     "
echo "*"
echo "*  ''  ,    "
echo "*    "
echo "*"
echo "*  :"
echo "*     = $KB"
echo "**************************************************************"

for host in $PCH_HOSTS; do
	host_array[${#host_array[*]}]="$host"
done

GROUP_BY="client_address"
GROUP_AS=""

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

	HEADER="total_bytes - hit_bytes AS \"\","
	HEADER2="ROUND (SUM (\"\")/$KB, 0) AS \"\","

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

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

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

	QUERY="$QUERY
		SELECT $GROUP_BY, $HEADER FROM proxy_log
		WHERE host like '${host_array[$count]}' $AND_FILTER"

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

QUERY="
	SELECT name AS \"$GROUP_AS\", $HEADER2 FROM ($QUERY) subselect, names
	WHERE $GROUP_BY = data
	GROUP BY name

	UNION

	SELECT $GROUP_BY AS \"$GROUP_AS\", $HEADER2 FROM ($QUERY) subselect
	WHERE $GROUP_BY NOT IN (SELECT data FROM names)
	GROUP BY $GROUP_BY

	ORDER BY \"\" DESC"

$PSQL -c "$QUERY"
