Changeable sort order [merge]

[daniel]

Some column headers in reports (all but summary?) could be links to select sort column and update the report.

Might be problematic if the SQL in the report contains "order by" or "group by", but we could easily check for that and disable the feature if found.

[daniel]

This is easiest done by resorting the result set in python upon return.

[rocky]

Basic sorting has been added (doesn't work if the reports have been grouped).

I checked out branches/0.6-stable (Mar/28/2004 12:45am EST). Here is the 'svn diff' output. Should be no problem applying this diff against main trunk also.

Index: trac/Report.py
===================================================================
--- trac/Report.py      (revision 322)
+++ trac/Report.py      (working copy)
@@ -20,6 +20,7 @@
 # Author: Jonas Borgström <jonas@edgewall.com>
  
 import os,os.path
+import types
 import time
 import re
  
@@ -35,6 +36,38 @@
 dynvars_disallowed_var_chars_re = re.compile('[^A-Z0-9_]')
 dynvars_disallowed_value_chars_re = re.compile('[^a-zA-Z0-9-_@.,]')
  
+try:
+    _StringTypes = [types.StringType, types.UnicodeType]
+except AttributeError:
+    _StringTypes = [types.StringType]
+
+class ColumnSorter:
+
+    def __init__(self, columnIndex, asc=1):
+        self.columnIndex = columnIndex
+        self.asc = asc
+
+    def sort(self, x, y):
+        const = -1
+        if not self.asc:
+            const = 1
+
+        # make sure to ignore case in comparisons
+        realX = x[self.columnIndex]
+        if type(realX) in _StringTypes:
+            realX = realX.lower()
+        realY = y[self.columnIndex]
+        if type(realY) in _StringTypes:
+            realY = realY.lower()
+
+        result = 0
+        if realX < realY:
+            result = const * 1
+        elif realX > realY:
+            result = const * -1
+
+        return result
+
 class Report (Module):
     template_name = 'report.cs'
     template_rss_name = 'report_rss.cs'
@@ -72,6 +105,10 @@
                                 'Invalid Report Number')
                 title = row[0]
                 sql   = self.sql_sub_vars(row[1], args)
+
+                if sql.find('__group__') == -1:
+                    self.cgi.hdf.setValue('report.sorting.enabled', '1')
+
                 cursor.execute(sql)
             except Exception, e:
                 self.error = e
@@ -177,6 +214,7 @@
         for col in self.cols:
             title=col[0].capitalize()
             prefix = 'report.headers.%d' % idx
+            self.cgi.hdf.setValue('report.headers.%d.real' % idx, col[0])
             if title[:2] == '__' and title[-2:] == '__':
                 continue
             elif title[0] == '_' and title[-1] == '_':
@@ -190,6 +228,22 @@
             self.cgi.hdf.setValue(prefix, title)
             idx = idx + 1
  
+        if self.args.has_key('sort'):
+            sortCol = self.args['sort']
+            colIndex = None
+            x = 0
+            while colIndex == None and x < len(self.cols):
+                if self.cols[x][0] == sortCol:
+                    colIndex = x
+                x = x + 1
+            if colIndex != None:
+                if self.args.has_key('asc'):
+                    sorter = ColumnSorter(colIndex, int(self.args['asc']))
+                else:
+                    sorter = ColumnSorter(colIndex)
+                self.rows.sort(sorter.sort)
+
+
         # Convert the rows and cells to HDF-format
         row_idx = 0
         for row in self.rows:
Index: templates/report.cs
===================================================================
--- templates/report.cs (revision 322)
+++ templates/report.cs (working copy)
@@ -42,7 +42,19 @@
          <?cs if $header.fullrow ?>
            </tr><tr><th class="header-left" colspan="100"><?cs var:header ?></th>
          <?cs else ?>
-           <th class="header-left"><?cs var:header ?></th>
+
+           <?cs if $report.sorting.enabled ?>
+             <?cs set sortValue = '' ?>
+             <?cs if $header.real == $Query.sort ?>
+               <?cs set sortValue = '?sort='+$header.real+'&asc=0' ?>
+             <?cs else ?>
+               <?cs set sortValue = '?sort='+$header.real+'&asc=1' ?>
+             <?cs /if ?>
+             <th class="header-left"><a href="<?cs var:sortValue ?>"><?cs var:header ?></a></th>
+           <?cs else ?>
+             <th class="header-left"><?cs var:header ?></th>
+           <?cs /if ?>
+
            <?cs if $header.breakrow ?>
               </tr><tr>
            <?cs /if ?>
@@ -170,13 +182,21 @@
    <?cs set vars=$vars+'&'+name($arg)+'='+$arg ?>
  <?cs /each ?>
  
+ <?cs set sortInfo='' ?>
+ <?cs if Query.sort ?>
+   <?cs set sortInfo=$sortInfo+'&sort='+$Query.sort ?>
+ <?cs /if ?>
+ <?cs if Query.asc ?>
+   <?cs set sortInfo=$sortInfo+'&asc='+$Query.asc ?>
+ <?cs /if ?>
+
   <div id="main-footer">
    Download report in other data formats: <br />
    <a class="noline" href="?format=rss"><img src="<?cs var:htdocs_location
  ?>xml.png" alt="RSS Feed" style="vertical-align: bottom"/></a>&nbsp;
-   <a href="?format=rss<?cs var $vars ?>">(RSS 2.0)</a>&nbsp;|
-   <a href="?format=csv<?cs var $vars ?>">Comma-delimited</a>&nbsp;|
-   <a href="?format=tab<?cs var $vars ?>">Tab-delimited</a>
+   <a href="?format=rss<?cs var $vars ?><?cs var $sortInfo ?>">(RSS 2.0)</a>&nbsp;|
+   <a href="?format=csv<?cs var $vars ?><?cs var $sortInfo ?>">Comma-delimited</a>&nbsp;|
+   <a href="?format=tab<?cs var $vars ?><?cs var $sortInfo ?>">Tab-delimited</a>
    <br />
   </div>
  <?cs /if ?>

[Jonas Borgström]

Moving this to 0.7

[daniel]

Merged/Fixed in [370].

[daniel]

Sort order switching isnt working.

[rocky]

Resolved sort order switching problem with the following patch.

Index: trac/Report.py
===================================================================
--- trac/Report.py      (revision 390)
+++ trac/Report.py      (working copy)
@@ -236,10 +236,13 @@
                     colIndex = x
                 x = x + 1
             if colIndex != None:
+                k = 'report.headers.%d.asc' % (colIndex-1)
                 if self.args.has_key('asc'):
                     sorter = ColumnSorter(colIndex, int(self.args['asc']))
+                    self.req.hdf.setValue(k, self.args['asc'])
                 else:
                     sorter = ColumnSorter(colIndex)
+                    self.req.hdf.setValue(k, '1')
                 self.rows.sort(sorter.sort)
                                                                                
                                                                                
Index: templates/report.cs
===================================================================
--- templates/report.cs (revision 390)
+++ templates/report.cs (working copy)
@@ -44,7 +44,7 @@
          <?cs else ?>
            <?cs if $report.sorting.enabled ?>
              <?cs set sortValue = '' ?>
-             <?cs if $header.real == $Query.sort ?>
+             <?cs if $header.asc == '1' ?>
                <?cs set sortValue = '?sort='+$header.real+'&asc=0' ?>
              <?cs else ?>
                <?cs set sortValue = '?sort='+$header.real+'&asc=1' ?>

[Jonas Borgström]

Fixed in [398].