-# construct WHERE clause
-# $QueryGroupList is "list of newsgroup" (or 1),
-# $QueryThreshold is threshold definition (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 AND %s %s',$QueryGroupList,$QueryThreshold,&SQLHierarchies($Options{'s'}));
-
-# get length of longest newsgroup delivered by query for formatting purposes
-# FIXME
-my $MaxLength = &GetMaxLenght($DBHandle,$Conf{'DBTableGrps'},'newsgroup',$WhereClause,$StartMonth,$EndMonth,(@GroupList,@Params));
-
-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): top or flop list?
- if ($Options{'i'}) {
- $OrderClause = 'postings';
- } else {
- $OrderClause = 'postings DESC';
- };
- # set -b to 10 if < 1 (Top 10)
- $Options{'b'} = 10 if $Options{'b'} !~ /^\d*$/ or $Options{'b'} < 1;
- # push LIMIT to Params
- push @Params,$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
- # which kind of level (-i): more than -l x or less than -l x?
- my ($Level);
- if ($Options{'i'}) {
- $Level = '<';
- } else {
- $Level = '>';
- };
- # prepare and execute query: get list of newsgroups meeting level condition
- $DBQuery = $DBHandle->prepare(sprintf("SELECT newsgroup FROM %s.%s WHERE %s GROUP BY newsgroup HAVING MAX(postings) %s ?",$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$WhereClause,$Level));
- $DBQuery->execute($StartMonth,$EndMonth,@GroupList,$Options{'l'})
- or die sprintf("$MySelf: E: Can't get groups data for %s to %s from %s.%s: %s\n",$StartMonth,$EndMonth,$Conf{'DBDatabase'},$Conf{'DBTableGrps'},$DBI::errstr);
+### get sort order and build SQL 'ORDER BY' clause
+# default to 'newsgroup' for $OptBoundType 'level' or 'average'
+$OptGroupBy = 'newsgroup' if (!$OptGroupBy and
+ $OptBoundType and $OptBoundType ne 'default');
+# force to 'month' for $OptReportType 'average' or 'sum'
+$OptGroupBy = 'month' if ($OptReportType and $OptReportType ne 'default');
+# parse $OptGroupBy to $GroupBy, create ORDER BY clause $SQLOrderClause
+my ($GroupBy,$SQLOrderClause) = SQLSortOrder($OptGroupBy, $OptOrderBy);
+# $GroupBy will contain 'month' or 'newsgroup' (parsed result of $OptGroupBy)
+# set it to 'month' or 'key' for OutputData()
+$GroupBy = ($GroupBy eq 'month') ? 'month' : 'key';
+
+### get report type and build SQL 'SELECT' query
+my $SQLSelect;
+my $SQLGroupClause = '';
+my $Precision = 0; # number of digits right of decimal point for output
+if ($OptReportType and $OptReportType ne 'default') {
+ $SQLGroupClause = 'GROUP BY newsgroup';
+ # change $SQLOrderClause: replace everything before 'postings'
+ $SQLOrderClause =~ s/BY.+postings/BY postings/;
+ if ($OptReportType eq 'average') {
+ $SQLSelect = "'All months',newsgroup,AVG(postings)";
+ $Precision = 2;
+ # change $SQLOrderClause: replace 'postings' with 'AVG(postings)'
+ $SQLOrderClause =~ s/postings/AVG(postings)/;
+ } elsif ($OptReportType eq 'sum') {
+ $SQLSelect = "'All months',newsgroup,SUM(postings)";
+ # change $SQLOrderClause: replace 'postings' with 'SUM(postings)'
+ $SQLOrderClause =~ s/postings/SUM(postings)/;
+ }
+ } else {
+ $SQLSelect = 'month,newsgroup,postings';
+};
+
+### get length of longest newsgroup name delivered by query
+### for formatting purposes
+my $Field = ($GroupBy eq 'month') ? 'newsgroup' : 'month';
+my $MaxLength = &GetMaxLength($DBHandle,$Conf{'DBTableGrps'},
+ $Field,$SQLWhereClause,$SQLHavingClause,
+ @SQLBindNewsgroups);
+
+### build and execute SQL query
+my ($DBQuery);
+# special query preparation for $OptBoundType 'level', 'average' or 'sums'
+if ($OptBoundType and $OptBoundType ne 'default') {
+ # prepare and execute first query:
+ # get list of newsgroups meeting level conditions
+ $DBQuery = $DBHandle->prepare(sprintf('SELECT newsgroup FROM %s.%s %s '.
+ 'GROUP BY newsgroup %s',
+ $Conf{'DBDatabase'},$Conf{'DBTableGrps'},
+ $SQLWhereClause,$SQLHavingClause));
+ $DBQuery->execute(@SQLBindNewsgroups)
+ or &Bleat(2,sprintf("Can't get groups data for %s from %s.%s: %s\n",
+ $CaptionPeriod,$Conf{'DBDatabase'},$Conf{'DBTableGrps'},
+ $DBI::errstr));