-# construct WHERE clause
-# $QueryPart is "list of newsgroup" (or 1),
-# &SQLHierarchies() takes care of the exclusion of hierarchy levels (.ALL)
-# according to setting of -s
-my $WhereClause = sprintf('month BETWEEN ? AND ? AND %s %s',$QueryPart,&SQLHierarchies($Options{'s'}));
-
-# get lenght of longest newsgroup delivered by query for formatting purposes
-# FIXME
-my $MaxLength = &GetMaxLenght($DBHandle,$Conf{'DBTableGrps'},'newsgroup',$WhereClause,$StartMonth,$EndMonth,@GroupList);
-
-my ($OrderClause,$DBQuery);
-# -b (best of / top list) defined?
-if (!defined($Options{'b'}) and !defined($Options{'l'})) {
- # default: neither -b nor -l
- # set ordering (ORDER BY) to "newsgroups" or "postings", "ASC" or "DESC"
- # according to -q and -d
- $OrderClause = 'newsgroup';
- $OrderClause = 'postings' if $Options{'q'};
- $OrderClause .= ' DESC' if $Options{'d'};
- # prepare query: get number of postings per group from groups table for given months and newsgroups
- $DBQuery = $DBHandle->prepare(sprintf("SELECT month,newsgroup,postings FROM %s.%s WHERE %s ORDER BY month,%s",$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$WhereClause,$OrderClause));
-} elsif ($Options{'b'}) {
- # -b is set (then -l can't be!)
- # set sorting order (-i)
- if ($Options{'i'}) {
- $OrderClause = 'postings';
- } else {
- $OrderClause = 'postings DESC';
- };
- # push LIMIT to GroupList to match number of binding vars for DBQuery->execute
- push @GroupList,$Options{'b'};
- # prepare query: get sum of postings per group from groups table for given months and newsgroups with LIMIT
- $DBQuery = $DBHandle->prepare(sprintf("SELECT newsgroup,SUM(postings) AS postings FROM %s.%s WHERE %s GROUP BY newsgroup ORDER BY %s,newsgroup LIMIT ?",$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$WhereClause,$OrderClause));
-} else {
- # -l must be set now, as all other cases have been taken care of
- # set sorting order (-i)
- if ($Options{'i'}) {
- $OrderClause = '<';
- } else {
- $OrderClause = '>';
- };
- # push level and $StartMonth,$EndMonth - again - to GroupList to match number of binding vars for DBQuery->execute
- # FIXME -- together with the query (see below)
- push @GroupList,$Options{'l'};
- push @GroupList,$StartMonth,$EndMonth;
- # prepare query: get number of postings per group from groups table for given months and
- # FIXME -- this query is ... in dire need of impromevent
- $DBQuery = $DBHandle->prepare(sprintf("SELECT month,newsgroup,postings FROM %s.%s WHERE newsgroup IN (SELECT newsgroup FROM %s.%s WHERE %s GROUP BY newsgroup HAVING MAX(postings) %s ?) AND %s ORDER BY newsgroup,month",$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$WhereClause,$OrderClause,$WhereClause));
-};